Re: ASM directory path info from ASM instance

mhthomas wrote:
> Hi,
> Version
> OS Linux
> RAC, Standard Edition
> 1) Does anyone know how to get the ASM file name, including full
> directory path, from just an ASM instance, e.g. without looking from
> the ASM client database.
> 3) The closest I get is the alias name (v$asm_alias) without directory
> path, with the following query, but need the full directory info, e.g.
> --
> col group_number heading 'GR|NO' format 99
> col file_number heading 'FILE|NO' format 9999
> col redundancy format a6 noprint
> col striped format a6 noprint
> --
> select a.group_number, a.file_number, a.compound_index, a.incarnation,
> a.block_size, a.blocks, a.bytes/(1024*1024) mbytes,*1024) 
> mspace, 
> a.type, a.redundancy, a.striped, a.creation_date, a.modification_date,
> from v$asm_file a, v$asm_alias b
> where a.type != 'ARCHIVELOG'    -- avoid for readability
> and a.group_number = b.group_number
> and a.file_number = b.file_number
> and a.incarnation = b.file_incarnation
> order by a.type, a.file_number;
> --

I haven't included everything you want, but I think the following query 
gets you closer, in that it does show the complete ASM directory 
(folder) path using only the ASM instance.  You might also find an outer 
join and a predicate involving V$ASM_FILES.ALIAS_DIRECTORY to be useful, 
although I haven't tried it.

select, CONNECT_BY_ROOT root,
from v$asm_alias b
connect by prior b.reference_index = b.parent_index

Mark Bole


Other related posts: