Wednesday, February 8, 2017

How do I find the size of a schema in my database?


It depends on how you define "size".  If you want the total disk space consumed by the schema (including indexes, tablespace free space), then the SQL query is quite simple:

select 
   owner,
   sum(bytes)/1024/1024/1024 schema_size_GB
from
   dba_segments
group by
   owner;

If you just want the size of the schema in terms of the space consumed by table rows, you would use a different query to calculate the schema size:

select
   sum(bytes)/1024/1024/1024 as size_in_GB,
   segment_type
from
   dba_segments
where
   owner='SCOTT'
group by
   segment_type;

No comments:

Post a Comment