Oracle: tablespace size and biggest segments
Queries for used and free space per tablespace and to list the biggest segments.
Three queries to check tablespace usage in an Oracle database and find the biggest segments. They need access to the DBA_* views.
Used, free and total space per tablespace
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
from (select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace,
tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
and df.totalspace <> 0;
Biggest segments of a tablespace
Here for SYSTEM; change the name to inspect another tablespace.
SELECT owner, segment_name, segment_type, bytes/(1024*1024) size_m
FROM dba_segments
WHERE tablespace_name = 'SYSTEM'
ORDER BY size_m DESC;
Segments of a schema
select owner,
segment_name,
partition_name,
segment_type,
bytes / 1024/1024 "MB"
from dba_segments
where owner = '<owner_name>';
Free space taking autoextend into account
The first query only counts the current size of the datafiles. To see the free space against the maximum size the datafiles can reach (autoextend), Tim Hall publishes a ready-made script, ts_free_space.sql, on Oracle-Base.
Notes
dba_segmentsonly lists allocated segments: free space inside the datafiles that is fragmented or not yet reusable is not visible here.