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

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Start Discussion

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.