Skip to content

How to Check Schema Size in Oracle

  • by

Schema Size Calculation

After knowing the whole database size, we may drill down a little bit to calculate schema size for every user in database.

1. All Schema Size

To list all schema size from top to down, user by user.

SQL> column "Schema" format a20;
SQL> column "Size (MB)" format 999.99;
SQL> select owner "Schema", sum(bytes)/1024/1024 "Size (MB)" from dba_segments group by owner order by 2 desc;

Schema               Size (MB)
-------------------- ---------
SYS                     458.56
SH                      158.00
MDSYS                   146.88
XDB                      61.13
OE                       10.00
WMSYS                     6.56
PM                        5.88
DVSYS                     4.56
CTXSYS                    2.81
AUDSYS                    2.06
IX                        1.94

Schema               Size (MB)
-------------------- ---------
HR                        1.56
ORDDATA                   1.31
SYSTEM                    1.31
GSMADMIN_INTERNAL         1.00
OJVMSYS                    .38
ORDSYS                     .38
LBACSYS                    .31
DBSNMP                     .19

19 rows selected.

The key concept of the above SQL query is to use aggregation function SUM and conjunct with GROUP BY clause.

2. Top 10 Schema Size

We list only top 10 user's schema size.

SQL> select * from (select owner "Schema", sum(bytes)/1024/1024 "Size (MB)" from dba_segments group by owner order by 2 desc) where rownum <= 10;

Schema               Size (MB)
-------------------- ---------
SYS                     458.56
SH                      158.00
MDSYS                   146.88
XDB                      61.13
OE                       10.00
WMSYS                     6.56
PM                        5.88
DVSYS                     4.56
CTXSYS                    2.81
AUDSYS                    2.06

10 rows selected.

3. Selected Schema Size

We selectively list only users we interested in.

SQL> select owner "Schema", sum(bytes)/1024/1024 "Size (MB)" from dba_segments where owner in ('HR', 'IX', 'OE', 'PM', 'SH') group by owner order by 2 desc;

Schema               Size (MB)
-------------------- ---------
SH                      158.00
OE                       10.00
PM                        5.88
IX                        1.94
HR                        1.56

Further reading: Segments vs Schema Objects.

Leave a Reply

Your email address will not be published.