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.