How to collect the full path name of the files in ASM diskgroups [ID 888943.1]

Applies to:

Oracle Server - Enterprise Edition - Version 10.1.0.2 to 11.2.0.2 [Release 10.1 to 11.2]
Information in this document applies to any platform.
Information in this document applies to any platform.

Goal

How to collect the full path name of the files in ASM diskgroups

Fix

Set your ORACLE_SID to the ASM instance name.

Connect to the ASM instance:
in 10g: sqlplus / as sysdba
in 11g: sqlplus / as sysasm

Then perform. the following query: 

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)
START WITH (mod(pindex, power(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex;

---

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27348/viewspace-759898/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27348/viewspace-759898/