Before an upgrade, a migration or a storage review, you need a quick picture of where the space goes. These queries read DBA_SEGMENTS and ALL_TABLES; results are in gigabytes unless stated otherwise.

1. Total size of the database

SELECT SUM(bytes/1024/1024/1024) FROM dba_segments;

2. Size by tablespace

SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
FROM dba_segments
GROUP BY tablespace_name
ORDER BY size_gb DESC;

3. Size by schema (owner)

SELECT owner, SUM(bytes/1024/1024/1024) FROM dba_segments
GROUP BY owner
ORDER BY owner;

4. Fragmented space for a schema

For each table of a schema, compare the space allocated (blocks) with the space the rows actually need (num_rows × avg_row_len). A high percentage points to tables worth reorganising (for example with ALTER TABLE … MOVE or an online redefinition).

SELECT
  table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
  round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
  round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
  round((round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100,2) "percentage"
FROM
  all_tables
WHERE
  owner='<OWNER>'
  AND blocks>0;

Two things to keep in mind:

  • 16 is the block size in KB (DB_BLOCK_SIZE = 16384). Replace it with your own block size.
  • The figures come from optimizer statistics (NUM_ROWS, AVG_ROW_LEN, BLOCKS), so gather fresh statistics on the schema first.