Check Index information in table of Oracle

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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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.