Oracle Query to check the actual amount of free space in a tablespace.

Oracle provides data dictionary views to display detailed information about space usage in a tablespace.

Following commands uses dba_data_files and dba_free_space views to display the actual amount of free space, used space, total space and pct free within an oracle tablespace:

--------------------------------------------------------------------------------
-- Query to display the actual amount of free space in Oracle tablespace.
--------------------------------------------------------------------------------

column "Tablespace" format a13
column "Used MB"    format 99,999,999
column "Free MB"    format 99,999,999
column "Total MB"   format 99,999,999

SELECT   fs.tablespace_name "Tablespace",
         (df.totalspace - fs.freespace) "Used MB",
         fs.freespace "Free MB",
         df.totalspace "Total MB",
         ROUND (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;

Tablespace        Used MB     Free MB    Total MB  Pct. Free
------------- ----------- ----------- ----------- ----------
SYSAUX                473         127         600         21
UNDOTBS1              123         552         675         82
USERS                   1           4           5         80
SYSTEM                669          31         700          4

You may also like...

1 Response

  1. Prathamesh says:

    Very Useful info

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: