How to Display Oracle Schema Size with SQL Query

Query to find the tables size occupied in GB in Oracle SQL for various SCHEMAS

Here is a fairly accurate query that takes tables, indexes, and any partitioned tables/indexes into account for all tables for a single schema owner. Note that there will be some additional overhead in actual space used due to blocksize, file headers, etc.

  select segment_name
         , segment_type
         , sum(bytes)/1024/1024/1024 as GB
         , ROUND(100*RATIO_TO_REPORT(bytes) OVER (), 4) PERCENTAGE
  from dba_segments ds
  where
      owner ='SCOTT'
      and (
              segment_type like 'TABLE%'
              and segment_name in ('EMP', 'SALGRADE', 'EMP_BKP', 'DEPT')
          or
              (
                 segment_type like 'INDEX%'
                 and segment_name in (
                         select index_name from dba_indexes di
                             where owner ='SCOTT'
                               and di.table_name in ('EMP', 'SALGRADE', 'EMP_BKP', 'DEPT')
                         )
              -- more fun here for LOBs, etc.
             )
         )
  group by segment_name, segment_type, bytes
  order by GB desc;

Take a look at DBA_LOBS for additional criteria to add to the -- more fun here to pick up LOB information.

Compute the Database Schema Size

If this is for Oracle:

Space allocated to tables and indexes:

select owner, segment_type, segment_name, sum(bytes)
from dba_segments
group by owner, segment_type, segment_name;

Free space:

select tablespace_name, sum(bytes) from dba_free_space group by tablespace_name;

Another more detailed query here: http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551402703363001567

Row Count and Size For Each Table Under Oracle Schema

select     table_name,     num_rows, bytes/1047586 MB
 from     dba_tables t
Inner join dba_segments s
  On table_name =segment_name and t.owner =s.owner
Where segment_type='TABLE'
And s.owner='schema'

---

Related Topics