tblspc_free.sql

by

This script help you to find the information about tablespace usage in oracle database.

Call the script @tblspc_free.sql

SET LINESIZE 200
SET PAGESIZE 66
COLUMN pct_free FORMAT 999.99 HEADING "% Free"
COLUMN name FORMAT A20 HEADING "Tablespace Name"
COLUMN mbytes FORMAT 99,999,999 HEADING "Total MBytes"
COLUMN used FORMAT 99,999,999 HEADING "Used Mbytes"
COLUMN free FORMAT 99,999,999 HEADING "Free Mbytes"

BREAK ON REPORT
COMPUTE SUM OF mbytes ON REPORT
COMPUTE SUM OF free ON REPORT
COMPUTE SUM OF used ON REPORT

SELECT fs.tablespace_name NAME, df.totalspace mbytes,
       (df.totalspace - fs.freespace) used, fs.freespace free,
       100 * (fs.freespace / df.totalspace) pct_free
  FROM (SELECT   tablespace_name, ROUND (SUM (BYTES) / 1048576) totalspace
            FROM dba_data_files
        GROUP BY tablespace_name) df,
       (SELECT   tablespace_name, ROUND (SUM (BYTES) / 1048576) freespace
            FROM dba_free_space
        GROUP BY tablespace_name) fs
 WHERE df.tablespace_name = fs.tablespace_name(+)
 and ('&1' IS NULL or df.tablespace_name= '&1')
 order by pct_free asc;

Please share this Blog with your colleagues or friends. Your suggestions and feedback are very helpful for everyone who come to this site and learn it from oracleocpworld.com.
Please comment here for your any query related to above post. You can email me on : oracleocpworld@gmail.com.

Leave a Comment