Skip to content

Database size

1. Full size of the database

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

2. Size by tablespace

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

3. Fragmented space for a schema

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
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;

Comments