Re: ASM directory path info from ASM instance

mhthomas wrote:
> Hi,
>
> Version 10.1.0.3
> OS Linux
> RAC, Standard Edition
> ASM
>
> 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, a.space/(1024*1024) mspace,
> a.type, a.redundancy, a.striped, a.creation_date, a.modification_date, b.name
> 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
b.name, CONNECT_BY_ROOT b.name root,
SYS_CONNECT_BY_PATH(b.name, '/') path
from v$asm_alias b
connect by prior b.reference_index = b.parent_index

-- 
Mark Bole
http://www.bincomputing.com



--
http://www.freelists.org/webpage/oracle-l