set pagesize 9999
set linesize 160
column tablespaceName heading 'Tablespace'
column kBytesUsed heading 'Used (KB)'
column kBytesFree heading 'Free (KB)'
column largest heading 'Largest'
column percentUsed heading '% Used'
select a.TABLESPACE_NAME tablespaceName,
a.BYTES / 1024 kBytesUsed,
b.BYTES / 1024 kBytesFree,
b.largest / 1024 largest,
round(((a.BYTES - b.BYTES)
/ a.BYTES)
*100
,2) percentUsed
from (
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
) a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
) b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc
;
Example:
Tablespace Used (KB) Free (KB) Largest % Used
------------------------------ ---------- ---------- ---------- ----------
SYSTEM 716800 7808 7104 98.91
SYSAUX 601024 29120 29120 95.15
EXAMPLE 102400 22848 20416 77.69
USERS 5120 1920 1920 62.5
UNDOTBS1 81920 60416 45888 26.25
5 rows selected.
