View the tablespace usage

• View the tablespace usage

The query below is useful for showing how full each tablespace in a database. 
(This works much like df on a filesystem. This query is just selecting the tablespace usage from a database).

SQL>set pages 999

col tablespace_name format a40

col "size MB" format 999,999,999

col "free MB" format 99,999,999

col "% Used" format 999

select    tsu.tablespace_name, ceil(tsu.used_mb) "size MB"

,   decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"

,   decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,

               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"

from   (select tablespace_name, sum(bytes)/1024/1024 used_mb

   from    dba_data_files group by tablespace_name union all

   select    tablespace_name || '  **TEMP**'

   ,   sum(bytes)/1024/1024 used_mb

   from    dba_temp_files group by tablespace_name) tsu

,   (select tablespace_name, sum(bytes)/1024/1024 free_mb

   from    dba_free_space group by tablespace_name) tsf

where   tsu.tablespace_name = tsf.tablespace_name (+)

order   by 4

/

If You prefer the output in gigabytes and doesn't bother about the TEMP tablespace:

SQL>

set pages 999

col tablespace_name format a40

col "size GB" format 999,999.99

col "free GB" format 999,999.99

col "% Used" format 999

SELECT

  tsu.tablespace_name,

  tsu.used_gb "size GB",

  tsf.free_gb "free GB",

  DECODE(tsu.used_gb,0,100,100 - (tsf.free_gb/tsu.used_gb*100)) "% used"

FROM (

  SELECT

    tablespace_name,

    cast (sum(bytes)/power(1024,3) AS decimal(15,9)) used_gb

  FROM

    dba_data_files

  GROUP BY

    tablespace_name

  ) tsu,

  (

  SELECT

    tablespace_name,

    cast (sum(bytes)/power(1024,3) AS decimal(15,9)) free_gb

  FROM

    dba_free_space

  GROUP BY

    tablespace_name

  ) tsf

WHERE

  tsu.tablespace_name = tsf.tablespace_name (+)

ORDER BY 4

/