Translate into your own language

Friday, March 11, 2016

How to get the ASM disk group size in Oracle

How to get the ASM disk group size in Oracle:

DBA's are frequently getting questions from the APPS teams is: How Much Of Free Space Is Left For Our Database To Grow ?

So, if your database is using Oracle ASM which is the case for the overwhelming majority of our corporate oracle databases, then we have the following query:

 select distinct NAME, TOTAL_MB, FREE_MB from gv$asm_diskgroup
 where name in
 (
select distinct trim(REPLACE(REGEXP_SUBSTR(name,'[^/]+', 1, 1), '+',' ')) DG from     gv$datafile
union
select distinct trim(REPLACE(REGEXP_SUBSTR(name,'[^/]+', 1, 1), '+',' ')) DG from gv$tempfile
union
select distinct trim(REPLACE(REGEXP_SUBSTR(member,'[^/]+', 1, 1), '+',' ')) DG from gv$logfile
union
select distinct trim(REPLACE(REGEXP_SUBSTR(name,'[^/]+', 1, 1), '+',' ')) DG from gv$archived_log
where name is not null
);

Output:

NAME                               TOTAL_MB         FREE_MB
-----------------                      -------------------         -------------------
AB_DATA                         3327943              2884297

No comments:

Post a Comment