DOYENSYS Knowledge Portal




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




Monday, December 28, 2015

Query to find tablespace usage above 80%

set line 200
SELECT d.tablespace_name "TS Name", d.num_files "Num Files",
       d.asize "Size MB", NVL (f.freebytes, 0) "Free MB",
       (d.BYTES - NVL (f.freebytes, 0)) "Used MB",
       ((d.BYTES - NVL (f.freebytes, 0)) * 100) / d.asize "Percent Used"
  FROM (SELECT   tablespace_name, SUM (BYTES) / 1024 / 1024 freebytes
            FROM dba_free_space
        GROUP BY tablespace_name) f,
       (SELECT   tablespace_name, COUNT (DISTINCT (file_id)) num_files,
                 sum(greatest(maxbytes,bytes))/1024/1024 asize,
/* Below logic does not calculate size properly if maxbytes is less than bytes
                   SUM (DECODE (maxbytes, 0, BYTES, maxbytes))
                 / 1024
                 / 1024 asize,
*/
                 SUM (BYTES) / 1024 / 1024 BYTES
            FROM dba_data_files
        GROUP BY tablespace_name) d
 WHERE d.tablespace_name = f.tablespace_name(+)
   AND d.tablespace_name not like '%UNDO%'
   AND ROUND (((d.BYTES - NVL (f.freebytes, 0)) * 100) / d.asize) > 80;

1 comment:

Sridevi K said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.