Ask Question
Help Others
Home › Blogs › Zooz's blog

Get Table Sizes in your Schema

Thu, 07/03/2008 - 12:06pm — Zooz
No votes yet

If you want to see how much space your tables are taking in the database, log in to the shcema and execute this SQL.

SELECT   segment_name table_name, MAX (tablespace_name) tablespace_name,
         SUM (BYTES) / (1024 * 1024) table_size_mb
    FROM user_extents
   WHERE segment_name IN (SELECT tname
                            FROM tab
                           WHERE tabtype = 'TABLE')
GROUP BY segment_name
ORDER BY tablespace_name

The Above query tells you the table name, the table space it is residing in and the size in MB.

Thanks,

Zooz

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Thu, 07/03/2008 - 1:36pm
#1
Lepa's picture
Lepa
User offline. Last seen 3 days 19 hours ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Get Table Sizes in your Schema

To add to this, you can alter your current schema by using:

ALTER session SET current_schema = new-schema;

Also, if you want to avoid the schema thing all together, just replace the user_extents table with dba_extents table. You do however need to limit your where clause to go after specific OWNER or your SQL will take tremendous amount of time to execute.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Top
  • reply

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
10 + 8 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.