Top 5 ASM queries every DBA should know



Last updated on August 24th, 2016 at 06:48 pm

ASM is being used as storage environment in various organization.Here are Top 5 ASM queries every DBA  should know

QUERY TO FIND THE FILES IN USE BY AN ASM INSTANCE

col full_path format a50
col full_alias_path format a50
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;

QUERY TO DETECT FILES IN AN ASM DISKGROUP BEFORE DROPPING

col full_path format a50
col full_alias_path format a50
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
AND gname = ‘&1’)
START WITH (mod(pindex, power(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex;

QUERY TO DETERMINE WHAT DISKGROUPS EXIST AND HOW FULL THEY ARE
SELECT NAME,TOTAL_MB,USABLE_FILE_MB FROM V$ASM_DISKGROUP;

QUERY TO DETERMINE THE STATE AND BALANCE OF DISKGROUPS; SELECT NAME,STATE,UNBALANCED FROM V$ASM_DISKGROUP;

QUERY TO DETERMINE THE STATE OF THE DISKS WITHIN A DISKGROUP
col name format a12
col path format a25
col mount_status format a7
col header_status format a12
col mode_status format a7
col state format a8
SELECT D.NAME, D.PATH, D.MOUNT_STATUS, D.HEADER_STATUS, D.MODE_STATUS, D.STATE
FROM V$ASM_DISK D, V$ASM_DISKGROUP G
WHERE G.NAME = ‘&1’
AND D.GROUP_NUMBER = G.GROUP_NUMBER;


Leave a Reply