Jump to content
OYE

DISTINCT DATABASE STORAGE USAGE IN ASM

Recommended Posts


Hey Guys,

I am working in a RAC environment and have been struggling to get a perfect query to view ASM Storage usage based on specific and each databases in the ASM. Simply, i want a result that shows a database  and how much size that database is using (in total) in my ASM

I tried out some query out, but they don't return what i want.

My desired outcome looks like the below table

GNAME      DBNAME         MB               GB            #FILES
---------- ---------- -------------- ---------------- ---------------- 
DATAC1     MISU2         5,867,345          5,867         1,245
RECOC1     MISU2         5,901,360          5,763         1,119

Thanks Guys!!!!!
 

Share this post


Link to post
Share on other sites

Try this one on for size. I currently only have the one db on my ASM storage, so test it out on yours and see how you go.

I just had this on file in my script library, so credit to the (unknown) original author, whoever they are.

SQL> SELECT
  2      gname,
  3      dbname,
  4      file_type,
  5     
round(SUM(space)/1024/1024) mb,
  6     
round(SUM(space)/1024/1024/1024) gb,
  7      COUNT(*) "#FILES"
  8  FROM
  9      (   SELECT
 10              gname,
 11             
regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname,
 12              file_type,
 13              space
 14          FROM
 15              (   SELECT
 16                     
concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
 17                     
system_created,
 18                      file_type,
 19                      space,
 20                      gname
 21                  FROM
 22                      (   SELECT
 23                             
b.name            gname,
 24                             
a.parent_index    pindex,
 25                             
a.name            aname,
 26                             
a.reference_index rindex ,
 27                             
a.system_created,
 28                             
c.type file_type,
 29                             
c.space
 30                          FROM
 31                             
v$asm_alias a,
 32                             
v$asm_diskgroup b,
 33                             
v$asm_file c
 34                          WHERE
 35                             
a.group_number = b.group_number
 36                          AND
a.group_number = c.group_number(+)
 37                          AND
a.file_number = c.file_number(+)
 38                          AND
a.file_incarnation = c.incarnation(+) )
 39                  START WITH
(mod(pindex, power(2, 24))) = 0
 40                  AND rindex IN
 41                      (   SELECT
a.reference_index
 42                          FROM  
v$asm_alias a,
 43                                
v$asm_diskgroup b
 44                          WHERE 
a.group_number = b.group_number
 45                          AND   
mod(a.parent_index, power(2, 24)) = 0
 46                      ) CONNECT
BY prior rindex = pindex )
 47          WHERE NOT file_type IS
NULL
 48          and   system_created =
'Y' )
 49  GROUP BY
 50      gname,
 51      dbname,
 52      file_type
 53  ORDER BY
 54      gname,
 55      dbname,
 56      file_type
 57  /

 
GNAME      DBNAME     FILE_TYPE    
               MB         GB     #FILES
---------- ----------
-------------------- ---------- ---------- ----------
DATA       ASM       
ASMPARAMETERFILE              8          0          1
DATA       ASM        PASSWORD     
                0          0          1
DATA       db122asm   CONTROLFILE  
               96          0          1
DATA       db122asm   DATAFILE     
             3028          3          4
DATA       db122asm   ONLINELOG    
             1260          1          3
DATA       db122asm   PARAMETERFILE
                8          0          1
DATA       db122asm   TEMPFILE     
               48          0          1



 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×