set pages 999 lines 100 col "Tablespace" for a50 col "Size MB" for 999999999 col "%Used" for 999 col "Add (80%)" for 999999 select tsu.tablespace_name "Tablespace" , ceil(tsu.used_mb) "Size MB" , 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used" , ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)" from (select tablespace_name, sum(bytes)/1024/1024 used_mb from dba_data_files group by tablespace_name) tsu , (select ts.tablespace_name , nvl(sum(bytes)/1024/1024, 0) free_mb from dba_tablespaces ts, dba_free_space fs where ts.tablespace_name = fs.tablespace_name (+) group by ts.tablespace_name) tsf where tsu.tablespace_name = tsf.tablespace_name (+) and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80 order by 3,4 /
This Blog is also nick named as Oracle Slate, where we can chalk our experiences with Oracle Databases and related products of Oracle. This blog is very simple to understand and relatively useful for all the budding Oracle Dba's, Beginner Dba's, Dba's Under-Construction and Experienced Dba's...
Sunday, July 10, 2011
Tablespaces that are >=80% full, and how much to add to make them 80% again
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment