Free space in Oracle tablespaces

I occasionally want to know what tablespaces I have defined, how big they are and how much free space is available.

Thanks to Praveen at http://www.expertsharing.com/2008/02/26/calculate-size-of-tablespace-free-space-of-a-tablespace/ for providing the following query, which makes this information available easily.

set pages 999;
set lines 132;
SELECT * 
FROM 
( SELECT 
    c.tablespace_name,
    ROUND(a.bytes/1048576,2)                    MB_Allocated,
    ROUND(b.bytes/1048576,2)                    MB_Free,
    ROUND((a.bytes-b.bytes)/1048576,2)          MB_Used,
    ROUND(b.bytes/a.bytes * 100,2)              tot_Pct_Free,
    ROUND((a.bytes-b.bytes)/a.bytes,2) * 100    tot_Pct_Used
  FROM 
    ( SELECT 
        tablespace_name,
        SUM(a.bytes) bytes
      FROM 
        sys.DBA_DATA_FILES a
      GROUP BY 
        tablespace_name
    ) a,
    ( SELECT 
        a.tablespace_name,
        NVL(SUM(b.bytes),0) bytes
      FROM 
        sys.DBA_DATA_FILES a,
        sys.DBA_FREE_SPACE b
      WHERE 
        a.tablespace_name = b.tablespace_name (+)
        AND a.file_id = b.file_id (+)
      GROUP BY 
        a.tablespace_name
    ) b,
    sys.DBA_TABLESPACES c
  WHERE 
    a.tablespace_name = b.tablespace_name(+)
    AND a.tablespace_name = c.tablespace_name
  ) 
WHERE 
  tot_Pct_Used >=0
ORDER BY 
  tablespace_name;

About Mark Nelson

Mark Nelson is a Developer Evangelist at Oracle, focusing on microservices and messaging. Before this role, Mark was an Architect in the Enterprise Cloud-Native Java Team, the Verrazzano Enterprise Container Platform project, worked on Wercker, WebLogic and was a senior member of the A-Team since 2010, and worked in Sales Consulting at Oracle since 2006 and various roles at IBM since 1994.
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

3 Responses to Free space in Oracle tablespaces

  1. Pingback: Purging old instance data from SOA/BPM 11g | RedStack

  2. acveer says:

    Thanks a lot for this useful script. Very Very Handy.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s