About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Saturday, February 9, 2013

How to determine size of Schema or Index or Table in Oracle Database.

Size of a User or Schema

select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' group by owner;

Size of INDEX

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_name='INDEX_NAME' group by segment_name;
OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_name='INDEX_NAME' group by owner,segment_name;

List of Size of all INDEXES of a USER

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_type='INDEX' group by segment_name order by "SIZE in GB" desc;
 OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='INDEX' group by owner,segment_name order by "SIZE in GB" desc;

Sum of sizes of all indexes

select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='INDEX' group by owner;

Size of table

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_name='TABLE_NAME' group by segment_name;
OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_name='TABLE_NAME' group by owner,segment_name;

 List of Size of all tables of a USER

select segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from user_segments where segment_type='TABLE' group by segment_name order by "SIZE in GB" desc
 OR
select owner,segment_name,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='TABLE' group by owner,segment_name order by "SIZE in GB" desc;

Sum of sizes of all tables

select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='TABLE' group by owner;

I hope this article helped you.

 Regards,
Amit Rath
Posted by at
Labels:

6 comments:

  1. KhyalbaazJuly 12, 2013 at 5:40 PM

    Scripts are very helpfull..
    you should add some more..
    I am waiting..

    ReplyDelete
  2. UnknownJanuary 24, 2014 at 12:33 PM

    Hi Amit,

    Could you please help me to understand export/import operation performed on databases.

    Task: Refreshing one of the QA schemas with Prod schemas.

    I don't want to know the query part, my intention is to convey the procedure involved in export/import to our support team who will be carrying out the task. They will be coordinating with the DBAs, but I wanted to know what and all they need to be aware of before they initiate the activity.

    Please help me to understand.

    ReplyDelete
  3. AnonymousSeptember 11, 2014 at 10:30 AM

    Sir how to free tablespace like users,sysaux and non system tablespaces please suggest me right answer.

    ReplyDelete
  4. moin's dbaAugust 6, 2015 at 3:12 PM

    Hi Amit,

    Really helpful.

    Moin.

    ReplyDelete
  5. manaFebruary 14, 2018 at 12:03 AM

    Thank you ! really helpful

    ReplyDelete
  6. SantoshFebruary 27, 2020 at 2:52 PM

    Thanks for sharing. It helped to get the Index Size

    ReplyDelete

Subscribe to: Post Comments (Atom)