列出当前未打开的ASM文件

通用版本SQL

连接ASM实例上运行下面的语句，获取存储在ASM中且当前未被任何数据库客户端打开的文件列表。

-- Script to report the list of files stored in ASM and CURRENTLY NOT OPENED (Doc ID 552082.1)

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;

注意事项：

利用上面脚本输出文件路径进行资源回收的过程中，需要重点关注以下内容：

• 需要验证确认临时offline的文件
• 需要验证确认READ ONLY的表空间文件
• 参数文件 (spfile) 将始终报告为未打开。在删除文件之前，请务必仔细检查该文件

用于12C

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 distinct GROUP_KFFOF gnum, NUMBER_KFFOF filnum from X$KFFOF where NUMBER_KFFOF >= 256) 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
;

注意：

特别注意PDB中要使用的文件可能在上面列出了

显示ASM磁盘组的文件全路径

-- How to collect the full path name of the files in ASM diskgroups (Doc ID 888943.1)

SELECT gnum, filnum, concat('+' || gname, sys_connect_by_path(aname, '/'))
  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
        -- AND g.name = 'DATA'
       )
 START WITH (MOD(pindex, power(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex;

附录

参考文档

Script to report the list of files stored in ASM and CURRENTLY NOT OPENED (Doc ID 552082.1)

How to collect the full path name of the files in ASM diskgroups (Doc ID 888943.1)