Oracle: How to get the Schema size

From time to time, you may want to know the size of your schema in Oracle. The following will display the size of the schema DB_USER_SCHEMA in Megabytes. First, connect to the oracle Instance and execute the sql query.


swinful$> sqlplus DB_USER_SCHEMA/DB_USER_SCHEMA@DB_INSTANCE

SQL*Plus: Release 10.2.0.2.0 – Production on Sat Jan 20 00:14:38 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 – 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 – Production

SQL> SELECT sum(bytes)/1024/1024 FROM user_segments;

The output will look similar to the following:


SUM(BYTES)/1024/1024
--------------------
2552.75

SQL>