View ASM Aliases-Files-and-Directories

View ASM Aliases, Files, and Directories

V$ASM_ALIAS contains a record for each system-generated file name, user-defined alias, and directory for every currently mounted disk group.

Use a hierarchical query on V$ASM_ALIAS to reconstruct the hierarchy of files, aliases, and directories in ASM. Join V$ASM_ALIAS with V$ASM_FILE to display additional file information, such as the file type. V$ASM_ALIAS is also commonly joined with V$ASM_DISKGROUP to display the disk group name.

The asmcmd ls -l command lists the contents of a directory:

ASMCMD> ls -l +DATA/RACDB/DATAFILE

Type Redund Striped Time Sys Name

DATAFILE MIRROR COARSE JUL 31 14:00:00 Y EXAMPLE.264.692926563

DATAFILE MIRROR COARSE JUL 31 14:00:00 Y SYSAUX.257.692926339

DATAFILE MIRROR COARSE JUL 31 14:00:00 Y SYSTEM.256.692926339

DATAFILE MIRROR COARSE JUL 31 14:00:00 Y UNDOTBS1.258.692926341

DATAFILE MIRROR COARSE JUL 31 14:00:00 Y UNDOTBS2.265.692926841

DATAFILE MIRROR COARSE JUL 31 14:00:00 Y USERS.259.692926341

The following query shows an example of how to reconstruct the ASM file hierarchy using V$ASM_ALIAS in combination with other V$ASM views:

SQL> SELECT CONCAT('+'||GNAME, SYS_CONNECT_BY_PATH(ANAME, '/'))
  2    FULL_PATH, SYSTEM_CREATED, ALIAS_DIRECTORY, FILE_TYPE
  3  FROM ( SELECT B.NAME GNAME, A.PARENT_INDEX PINDEX,
  4           A.NAME ANAME, A.REFERENCE_INDEX RINDEX,
  5           A.SYSTEM_CREATED, A.ALIAS_DIRECTORY,
  6           C.TYPE FILE_TYPE
  7         FROM V$ASM_ALIAS A, V$ASM_DISKGROUP B, V$ASM_FILE C
  8         WHERE A.GROUP_NUMBER = B.GROUP_NUMBER
  9               AND A.GROUP_NUMBER = C.GROUP_NUMBER(+)
 10               AND A.FILE_NUMBER = C.FILE_NUMBER(+)
 11               AND A.FILE_INCARNATION = C.INCARNATION(+)
 12       )
 13  START WITH (MOD(PINDEX, POWER(2, 24))) = 0
 14  CONNECT BY PRIOR RINDEX = PINDEX;