Blog

In this article, I will provide a detailed guide on how to check index size in Oracle. We’ll walk you through each step of the process with proper screenshots. Let’s open VirtualBox and get started!

1. Particular Index size in Oracle:

COLUMN segment_name FORMAT A30
select segment_name,sum(bytes)/(1024*1024) "INDEX_SIZE_MB" from dba_extents where  segment_type='INDEX' and owner=upper('&OWNER') and segment_name =upper('&SEGMENT_NAME') group by segment_name;

Please provide the index owner and table name (segment_name) once asked to enter. In our example, we checked the size of the index called EMP_NAME_IX of the owner HR.

If you want to check the size in GB, then you can run the below query:

COLUMN segment_name FORMAT A30
select segment_name,sum(bytes)/(1024*1024*1024) "INDEX_SIZE_GB" from dba_extents where  segment_type='INDEX' and owner=upper('&OWNER') and segment_name =upper('&SEGMENT_NAME') group by segment_name;

2. Each INDEX Size under a Schema:

COLUMN table_name FORMAT A30
select segment_name table_name,sum(bytes)/(1024*1024*1024) INDEX_SIZE_MB 
from dba_extents where segment_type='INDEX' and owner=upper('&OWNER') group by segment_name order by 2 desc;

Just provide the schema/user/owner name and you will get all the index sizes under that schema. In this example, We have checked the size of all the indexes under the HR schema.

If you want to check the index size in GB then run the below query:

COLUMN table_name FORMAT A30
select segment_name table_name,sum(bytes)/(1024*1024*1024) INDEX_SIZE_GB 
from dba_extents where segment_type='INDEX' and owner=upper('&OWNER') group by segment_name order by 2 desc;

I hope you found this article helpful. Feel free to reach out if you have any questions. Looking for more? Dive into our comprehensive guide on How to check table size in Oracle.