Check the index detail like column, degree, last analyzed, status and tablespace in Oracle

The following query will provide you the information related to the index. It will give you all the index details needed in performance tuning and the rest info for creating a new index or checking the existing index.

Check the index name belong to the table

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


Check the details of the index like column, tablespace, degree and 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='HR' and a.table_name = 'EMPLOYEEs'
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='HR' and a.table_name = 'EMPLOYEES'
Order by a.table_name, a.Index_name,a.column_position,a.column_name;