DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, September 14, 2015

TABLESPACE USED AND FREE BASED ON PERCENTAGE :

               without undo

select a.tablespace_name "TABLESPACE NAME",round(((sum(round(a.bytes/1024/1024)) - round(nvl(b.free,0)/1024/1024))/sum(round(a.bytes/1024/1024)))*100,2) 
"PERCENTAGE USED" , round(nvl(b.free,0)/1024/1024) "FREE" from dba_data_files a, (select tablespace_name, sum(bytes) free from dba_free_space group by 
tablespace_name) b where a.tablespace_name = b.tablespace_name(+) and a.tablespace_name not like 'UNDO%' group by a.tablespace_name,b.free having round
(((sum(round(a.bytes/1024/1024)) - round(nvl(b.free,0)/1024/1024))/ sum(round(a.bytes/1024/1024)))*100,2) > 90 ; (Change this value based on requirement)

          with undo

select a.tablespace_name "TABLESPACE NAME",round(((sum(round(a.bytes/1024/1024)) - round(nvl(b.free,0)/1024/1024))/sum(round(a.bytes/1024/1024)))*100,2) "PERCENTAGE USED" , round(nvl(b.free,0)/1024/1024) "FREE" from dba_data_files a, (select tablespace_name, sum(bytes) free from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name(+) group bya.tablespace_name,b.free having round(((sum(round(a.bytes/1024/1024)) - round(nvl(b.free,0)/1024/1024))/ sum(round(a.bytes/1024/1024)))*100,2) > 90;


O/P:


TABLESPACE NAME                PERCENTAGE USED       FREE
------------------------------ --------------- ----------
APPS_TS_MEDIA                            97.03       8824
APPS_TS_TX_DATA                          97.32       4612
APPS_TS_TX_IDX                           95.01       6011

BACKUP                                   96.98       7768

No comments: