Header Ads

How to check index column on table in oracle

The following command with help you to show indexes for a particular table in Oracle use the following:

select index_name from dba_indexes where table_name='tablename';

When showing indexes, make sure that you are giving the right <tablename>.
Select the owner from dba_tables where table_name='<tablename>' ;

Also, make sure the tablename is in uppercase.
it might get a bit confusing as to what indexes a specific table might have, and what columns in the table are assigned to those indexes. This query will provide this information to you. It draws on the information in the dba_ind_columns data dictionary view:

set pages 999

break on table_name skip 2

column table_name  format a25
column index_name  format a25
column column_name format a25

order by

--------------- -------------------- -------------------- ----------
SCOTT           EMP                  PK_EMP               EMPNO

No comments:

Powered by Blogger.