SQL Query: How to Check Index Space

Here are two SQL queries to check index space. I have given here an example from the Oracle database. I have explained in a simplified manner to understand easily.

The way of logging in to the database tells which in-built database view you need to refer for querying index space tables.

For instance, if you log in as a user need to refer to user_segments; as a DBA, you need to refer to dba_segments.

DBA_Segments

select sum(bytes)/1024/1024 as "Index Size (MB)" 
from dba_segments 
where segment_name='&INDEX_NAME';

The sum(bytes)/1024/1024 expression gives space in MBs. Here, we don’t know index_name. The index name we need to get here.

The below query gives the index name.

select index_name from dba_indexes 
where table_name='&TABLE_NAME' and owner='&TABLE_OWNER';

Here is an SQL query to get the Table name, index name, and Table owner.

select OWNER,INDEX_NAME,STATUS,TABLE_NAME 
from dba_indexes where TABLE_NAME='&tablename';

select index_name, column_name from dba_ind_columns 
where table_name='&TABLE_NAME' and TABLE_OWNER='&OWNER';

User_segments

As a normal user, you can follow these SQL queries to get index space.

select sum(bytes)/1024/1024 as "Index Size (MB)" 
from user_segments 
where segment_name='&INDEX_NAME';

select index_name from user_indexes 
where table_name='&TABLE_NAME' and owner='&TABLE_OWNER';

select OWNER,INDEX_NAME,STATUS,TABLE_NAME 
from user_indexes where TABLE_NAME='&tablename';

select index_name, column_name from user_ind_columns 
where table_name='&TABLE_NAME' and TABLE_OWNER='&OWNER';

References