Tablespace usage on Oracle Database

Yes, I know: many scripts are available to get tablespaces usage from your Oracle database.

The one I’m going to give you is not magic, but it can handle some special situations and it can be easily integrate into a “custom metric” in Oracle Enterprise Manager.

The script runs on Oracle 10g and onward.

set linesize 100
set pagesize 100
col file_name for a60

select
	a.tablespace_name,
	round(SUM(a.bytes)/(1024*1024)) CURRENT_MB,
	round(SUM(decode(a.AUTOEXTENSIBLE, 'NO', A.BYTES/(1024*1024), GREATEST (a.MAXBYTES/(1024*1024),A.BYTES/(1024*1024))))) MAX_MB,
	round((SUM(a.bytes)/(1024*1024) - c.Free/1024/1024)) USED_MB,
	round((SUM(decode(a.AUTOEXTENSIBLE, 'NO', A.BYTES/(1024*1024), GREATEST (a.MAXBYTES/(1024*1024),A.BYTES/(1024*1024)))) - (SUM(a.bytes)/(1024*1024) - round(c.Free/1024/1024))),2) FREE_MB,
	round(100*(SUM(a.bytes)/(1024*1024) - round(c.Free/1024/1024))/(SUM(decode(a.AUTOEXTENSIBLE, 'NO', A.BYTES/(1024*1024),GREATEST (a.MAXBYTES/(1024*1024),A.BYTES/(1024*1024)))))) USED_PCT
from
	dba_data_files a,
	(
		SELECT
			d.tablespace_name ,sum(nvl(c.bytes,0)) Free
		FROM
			dba_tablespaces d,
			DBA_FREE_SPACE c
		WHERE
			d.tablespace_name = c.tablespace_name(+)
			--AND d.contents='PERMANENT'
			--AND d.status='ONLINE'
			group by d.tablespace_name
	) c
WHERE
	a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name, c.Free/1024
order by 6;

The output looks like:

TABLESPACE_NAME                CURRENT_SIZE_MB MAX_SIZE_MB    USED_MB    FREE_MB   USED_PCT
------------------------------ --------------- ----------- ---------- ---------- ----------
TOOLS                                       16         512          2        510          0
XDB_DAT1                                    16          64          1         63          2
UNDO01                                    5120        2048        136       1912          3
SYSAUX                                    3328       16384       3095      13289         19
SYSTEM                                    1152        4096        830       3266         20
MGMT_TABLESPACE                       32767,98       32768      32692         76        100


It will give you the following informations:

  • CURRENT_SIZE_MB: current size of the tablespace in MB
  • MAX_SIZE_MB: max size of all datafiles (in MB) if autoextend is in use
  • USED_MB: amount of MB used in all datafiles
  • FREE_MB: amount of free MB in all datafile
  • USED_PCT: percent of space used

It takes into account:

  • Mix between auto-extensible and not auto-extensible datafiles
  • Tablespaces with different block sizes
  • Datafiles with a size (BYTES) greater than maxsize (MAXBYTES)

The SQL script does not return TEMP tablespaces usage.

If you want to exclude UNDO tablespaces, you have to uncomment the line “AND d.contents=’PERMANENT'”.

If you want to exclude OFFLINE tablespaces, you have to uncomment the line “AND d.status=’ONLINE'”.

Stay tuned for more DBA stuff!

Leave a Reply

Your email address will not be published. Required fields are marked *