Best Practices from Oracle Development's A‑Team

Free space in Oracle tablespaces

Mark Nelson


This post provides a sample SQL to query tablespaces statistics.

Main Article

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.

# Copyright 2012 Oracle Corporation. # All Rights Reserved. # # Provided on an 'as is' basis, without warranties or conditions of any kind, # either express or implied, including, without limitation, any warranties or # conditions of title, non-infringement, merchantability, or fitness for a # particular purpose. You are solely responsible for determining the # appropriateness of using and assume any risks. You may not redistribute. 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;

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha