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 on the ASM.
Simply, i want a result that shows a database and how much size that database is using (in total) in my ASMI tried out some query,like the one mentioned here but they don't return what i want.
SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;
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
I'd be more than glad, this is my first time using this platform
Thanks Guys!!!
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