LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon