脚本

该脚本可以在 ASM 实例上运行，如下所示：

$ sqlplus '/ as sysdba'
sql>@files_not_opened

脚本如下：

set pagesize 0
set linesize 200
col full_alias_path format a80
/*+ ---------------------------------------------------------------
    Query will return all the files stored on ASM but not currenlty
    opened by any database client of the diskgroups
    ordered by group number, file type
    ---------------------------------------------------------------*/
select * from (
/*+ ----------------------------------------------------------------
    1st branch returns all the files stored on ASM
    -----------------------------------------------------------------*/
select  x.gnum,x.filnum,x.full_alias_path,f.ftype from (
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295
and x.gnum=f.gnum and x.filnum=f.filnum
MINUS
/*+ -------------------------------------------------------------
    2nd branch returns all the files stored on ASM
    and currently opened by any database client of the diskgroups
    -----------------------------------------------------------------*/
select x.gnum,x.filnum,x.full_alias_path,f.ftype
from ( select id1 gnum,id2 filnum from v$lock where type='FA' and (lmode=4 or lmode=2)) l,
(
SELECT gnum,filnum,concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
            a.reference_index rindex,a.group_number gnum,a.file_number filnum
      FROM v$asm_alias a, v$asm_diskgroup g
      WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex
) x,
(select group_number gnum,file_number filnum, type ftype from v$asm_file order by group_number,file_number) f
where x.filnum != 4294967295 and
x.gnum=l.gnum
and x.filnum=l.filnum 
and x.gnum=f.gnum and x.filnum=f.filnum) q
order  by q.gnum,q.ftype
;

准备工作

查询必须在 ASM 实例上执行。

需要挂载磁盘组。

目标

ASM 的特性之一是数据库整合。
单个磁盘组将是多个数据库的存储库。
结果，有大量的数据文件。
如果文件是使用 OMF（Oracle Managed Files）格式创建的，当在数据库端删除数据文件时，它也会在 ASM 端删除。
可能存在 ASM 端未删除数据文件的情况，尤其是在不使用 OMF 格式时。

对于包含大量数据文件的大型磁盘组，有必要充分利用存储空间并回收为数据库未使用的文件分配的空间。

以下脚本将提供存储在 ASM 中且当前未由磁盘组的任何数据库客户端打开的文件列表。
验证查询结果非常重要，尤其是对于可能已标记为临时脱机的文件。
它不适用于 READ ONLY 表空间，因为一旦文件被数据库打开，它就会被查询返回。

注意：参数文件 (spfile) 将始终报告为未打开。
在删除文件之前，请务必仔细检查该文件。

输出示例

这是脚本执行结果的第一个示例。
请注意，它包括所有类型的文件，如 ARCHIVELOGS、FLASHBACK LOGS、REDOLOGS。
一旦确认不再需要这些文件，就可以使用 ASMCMD 或者 SQL 运行命令 ALTER DISKGROUP ... DROP FILE '[path]'; 删除它们。

GNUM     FILNUM FULL_ALIAS_PATH
---------- ---------- ---------------------------------------------------------------------
         1        270 +DATA1/G102/ONLINELOG/group_2.270.645109903
         1       3247 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_2_seq_54.3247.645169463
         1       3249 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_1_seq_59.3249.645167259
         1       3250 +DATA1/G102/FLASHBACK/log_104.3250.645367249
         1       3251 +DATA1/G102/FLASHBACK/log_103.3251.645369221
         1       3252 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_1_seq_58.3252.645367297
         1       3254 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_2_seq_53.3254.645369195
         1       3255 +DATA1/G102/ARCHIVELOG/2008_01_30/thread_1_seq_57.3255.645266969
         1       3258 +DATA1/G102/FLASHBACK/log_13.3258.645786863

如前所述，查看脚本的结果非常重要。
在以下示例中，这些数据文件当前未打开，但它们属于当前关闭的数据库。

GNUM     FILNUM FULL_ALIAS_PATH
---------- ---------- ---------------------------------------------------------------------

         2        256 +DG_NETAPPS/V102/DATAFILE/UNDOTBS2.256.643465013
         2        257 +DG_NETAPPS/NETAPP/DATAFILE/SYSTEM.257.643455635
         2        258 +DG_NETAPPS/NETAPP/DATAFILE/SYSAUX.258.643455635
         2        259 +DG_NETAPPS/NETAPP/DATAFILE/UNDOTBS1.259.643455641
         2        260 +DG_NETAPPS/NETAPP/DATAFILE/USERS.260.643455643
         2        261 +DG_NETAPPS/NETAPP/CONTROLFILE/Current.261.643458451
         2        262 +DG_NETAPPS/NETAPP/ONLINELOG/group_1.262.643458461