--Preared by Vijay.Cherukuri
--To get the full asm end-to-end info
SET ECHO OFF
SET VERIFY OFF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 600
SET TRIMSPOOL ON
column path format a50
column name format a20
spool gen_fullasm_details.lst
prompt
prompt
prompt ***INSTANCE PARAMETERS***
select name||'===='||value from v$parameter where isdefault='FALSE';
prompt
prompt
prompt ***DISK GROUP INFO***
select name, state, type, total_mb, free_mb from v$asm_diskgroup;
prompt
prompt
prompt ***DISK INFO***
select name, path, mode_status, state, disk_number from v$asm_disk;
prompt
prompt
prompt ***DISK GROUP AND DISK INFO***
select a.group_number,a.mount_status,
a.redundancy,a.total_mb,a.free_mb,b.name,a.failgroup,
b.state,b.type,a.path
from
v$asm_disk a,
v$asm_diskgroup b
where
a.group_number=b.group_number
/
prompt
prompt
prompt ***ASM CLIENT INFO***
select INSTANCE_NAME,DB_NAME,STATUS,SOFTWARE_VERSION,COMPATIBLE_VERSION from v$asm_client;
prompt
prompt
prompt ***ASM FILE HIERARCHY INFO***
SELECT 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 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;
exit;