Get Table Sizes in your Schema

Your rating: None Average: 1.5 (2 votes)

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.



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!


Have a question? Please ask it on the forum instead.
Lepa's picture
User offline. Last seen 2 years 13 weeks 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