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 "TABLESPACE_NAME",
	round(a.current_mb) "CURRENT_SIZE_MB",
	round(a.max_mb) "MAX_SIZE_MB",
	round(a.current_mb - c.free) "USED_MB",
	round(a.max_mb - (a.current_mb - c.free)) "FREE_MB",
	round(((a.current_mb - c.free)*100)/a.max_mb) "USED_PCT"
FROM
	(
		SELECT
			tablespace_name,
			SUM(a.bytes)/(1024*1024) current_mb,
			SUM(decode(a.autoextensible, 'NO', a.bytes/(1024*1024), GREATEST (a.maxbytes/(1024*1024),a.bytes/(1024*1024)))) max_mb
		FROM
			dba_data_files a
		GROUP BY tablespace_name
	) a,
	(
		SELECT
			d.tablespace_name, sum(nvl(c.bytes/(1024*1024),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
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!

One thought on “Tablespace usage on Oracle Database

  1. Bonjour monsieur,

    serait-il possible de rajouter une petite colonne avec des *** en fonction du taux de remplissage ?

    Merci d’avance,

Leave a Reply

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