Check the index information in Oracle

Following query will provide you the information related to index. It will give you all the details of index need in performance tuning and rest info for creating new index or check the existing index.

Check the index name belong to table

select tablespace_name,index_name from dba_indexes where table_name = 'EMPLOYEE'


Check the details of index like column,table space,degree,last analyzed

set line 200 pages 1000
column b.tablespace_name format a10
column table_name format a10
column index_name format a25
column column_name format a16
column tablespace_name format a8
column last_analyzed format a9
column degree format a1
Select a.table_name, a.index_name, a.column_name,b.tablespace_name,b.last_analyzed,b.degree,b.status
FROM dba_ind_columns a,dba_indexes b where b.index_name = a.index_name and
a.table_owner='IC' and a.table_name = 'DOC_LOG'
Order by a.table_name, a.Index_name,a.column_position,a.column_name;


Check the index details as partitioned, Status ,Ascending or descending

set line 250 pages 1000
column b.tablespace_name format a10
column table_name format a10
column index_name format a20
column column_name format a16
column tablespace_name format a8
column last_analyzed format a9
column degree format a1
Select a.table_name, a.index_name, a.column_name,b.tablespace_name,b.partitioned,b.status,a.descend,b.uniqueness
FROM dba_ind_columns a,dba_indexes b where b.index_name = a.index_name and
a.table_owner='IC' and a.table_name = 'TRAN'
Order by a.table_name, a.Index_name,a.column_position,a.column_name;