How to check index on a Table and its Size in Oracle

You can check index on a table in Oracle using DBA_INDEXES view and using dba_segments you can check size of index in Oracle. To find index on a table and its size you can also use user_indexes and user_segment views.

Important view used in the Query

Useful views to get index details:
As a DBA - if connected as a sys user:
dba_indexes;
 dba_segments;
dba_ind_columns;
dba_ind_partitions; 

If you are connected as a normal user  use following views:
user_indexes;
user_segments;
user_ind_columns;
user_ind_partitions;  

Top 10 Ways to Get Current Date and Time in Oracle DB

Query to check Index on a Table in Oracle

NOTE: Please enter table_name and OWNER in UPPERCASE:

set lines 200
col index_name from a30
col column_name for a30

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

select index_name from user_indexes where table_name='&TABLE_NAME';

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

Query To Check Index Column on a Table in Oracle

set lines 200
col index_name from a30
col column_name for a30
select index_name, column_name from dba_ind_columns where table_name='&TABLE_NAME' and TABLE_OWNER='&OWNER';
 select index_name, column_name from user_ind_columns where table_name='&TABLE_NAME';

Top 10 Commands to Check CPU Information on Linux

Query to check index size in Oracle

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

Conclusion: After reading this post you will be able to check index on a table and index size in Oracle. You can also find index column in Oracle all other details from the above query. You can also check more about partition index from the dba_ind_partitions view.