ASM QUERIES

ASM DISK SPACE REPORT

set lines 255

col path for a35

col Diskgroup for a15

col DiskName for a25

col disk# for 999

col total_mb for 999,999,999

col free_mb for 999,999,999

compute sum of total_mb on DiskGroup

compute sum of free_mb on DiskGroup

break on DiskGroup skip 1 on report -

 set pages 255

select a.name DiskGroup, b.disk_number Disk#, b.name DiskName, 

   b.total_mb, b.free_mb, 

   -- b.path, 

   b.header_status

from v$asm_disk b, v$asm_diskgroup a

where a.group_number (+) =b.group_number

order by b.group_number, b.disk_number, b.name;

DISK GROUP USAGE

set lines 120

col "Redundancy" for a15

col "Diskgroup" for a12

select a.name "Diskgroup" , round(sum(b.total_mb)/1024,1) "Size GB",  

       round(sum(b.free_mb)/1024,1) "Free GB", 

       decode (a.type, 'EXTERN',round(sum(b.free_mb)/1024,1),'NORMAL',round(sum(b.free_mb)/1024/2,1),'HIGH',round(sum(b.free_mb)/1024/3,1))  "Usable GB", 

       decode (a.type, 'EXTERN',round(sum(b.free_mb)/1024/1024,1),'NORMAL',round(sum(b.free_mb)/1024/1024/2,1),'HIGH',round(sum(b.free_mb)/1024/1024/3,1))  "Usable TB", 

       round((sum(b.total_mb)-sum(b.free_mb))/sum(b.total_mb)*1000)/10 "Use%",

       a.type "Redundancy"

from v$asm_disk b, v$asm_diskgroup a 

where  a.group_number (+) =b.group_number 

group by a.name, a.type order by 1;

GROUP BY USAGE

set pages 100

col database for a15

SELECT NVL(dbname, '-- TOTAL') database, round(SUM(space)/1024/1024) mb_used, 

       round(SUM(space) / AVG(total_mb * 1024 * 1024) * 100, 2) pct_used

FROM (

  SELECT gname, file_type, space, aname, system_created, alias_directory,

         regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname, total_mb

    FROM (

      SELECT system_created, alias_directory, file_type,space, level, gname, aname,

          concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path, total_mb

        FROM (

          SELECT b.name gname, b.total_mb, a.parent_index pindex, a.name aname,

                 a.reference_index rindex, a.system_created, a.alias_directory,

                 c.type file_type, c.space

            FROM v$asm_alias a 

            JOIN v$asm_diskgroup b ON a.group_number = b.group_number

       LEFT JOIN v$asm_file c ON a.group_number = c.group_number

             AND a.file_number = c.file_number

             AND a.file_incarnation = c.incarnation

        ) START WITH (mod(pindex, power(2, 24))) = 0 AND rindex IN (

          SELECT a.reference_index FROM v$asm_alias a, v$asm_diskgroup b

           WHERE a.group_number = b.group_number

             AND (mod(a.parent_index, power(2, 24))) = 0

        ) CONNECT BY prior rindex = pindex

    ) WHERE NOT file_type IS NULL AND system_created = 'Y' )

GROUP BY ROLLUP (dbname)

/

                                                    

Oracle ASM USAGE

select name, total_mb, free_mb, 

       round(100*(total_mb-free_mb)/greatest(1,total_mb),0) as used_pct 

  from v$asm_diskgroup;