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>

Advertisements
This entry was posted in Oracle. Bookmark the permalink.

15 Responses to Oracle: How to get the Schema size

  1. Here’s a slightly more useful solution

    col tsize heading ‘Total|Schema|Size (Mb)’
    col owner head ‘Schema|Name’

    select owner, sum(bytes)/1024/1024 tsize
    from dba_segments
    group by owner
    order by tsize desc
    /

  2. swinful says:

    David-

    That works too, but one must be connected as ‘dba’ in order to run your query. However, I like the output. Thank You!

    -swinful

  3. random_dude says:

    @David, Mb is megabits…should be MB, for megabytes

  4. keerthi says:

    I found it very useful. Thanks for sharing this.

  5. Rash says:

    I am looking for a schema size with/without fragmentation.

  6. exta says:

    I know this beofre.. but any way thanks for posting it

  7. Seema Dave says:

    Exactly agreed with same query for fetching schema size from segments. Thanks for providing such great tip.

  8. Manx says:

    Hey this is really great..This is Exactly wat I wanted !! šŸ™‚ Thanks….

  9. swinful says:

    Hi Manx,

    I am glad you found this posting useful.

    Regards,
    -swinful

  10. priya says:

    Thanks for the posting. This was really usefull. If available can you share script to find the 10 largest tables in a database along with schema name or the tables that are least used? I need to ask the dev team to list those for purging as we are facing some disk space issue in my current env.

  11. tilak says:

    thank you

  12. Anonymous says:

    Exactly what I need.. Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s