dba_free_space
Name Type
----------------- -------------
TABLESPACE_NAME VARCHAR2(30)
FILE_ID NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
Lists all of the free extents in all tablespaces in the database. It's useful for seeing how full your tablespaces are getting. The following query uses dba_free_space to produce a space usage list for all tablespaces in a database:
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
/
Example output:
TABLESPACE_NAME size MB free MB %used
---------------------------------------- ------------ ----------- ------
USERS 5 5 8
ANDY 50 18 65
SYSAUX 240 10 96
SYSTEM 480 11 97
TEMP **TEMP** 20 0 100
UNDOTBS1 190 0 100
6 rows selected.
For more useful DBA queries click here.
|
