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

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;