Skip to main content

Database size

· One min read
Franck Blettner
Franck Blettner
Creator

Queries to inspect Oracle database size by tablespace and identify fragmented space per schema.

1. Full size of the database​

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

2. Size by tablespace​

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

3. Fragmented space for a schema​

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;