It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

Find indexes for a table

It is not unusual to wonder what indexes a specific table might have, and what columns in the table are assigned to those indexes. The following query will provide this information to you. It draws on the information in the dba_ind_columns data dictionary view:

SQL> column table_owner format a15
SQL> column table_name format a20
SQL> column index_name format a20
SQL> column column_name format a20
SQL> Select owner, table_name, index_name, column_name
2  FROM dba_ind_columns
3  Order by owner, table_name, column_name
4  Where owner=’SCOTT’
5  AND table_name=’EMP’;
--------------- -------------------- -------------------- ----------
SCOTT           EMP                  PK_EMP               EMPNO
In this example we find that the EMP table in the SCOTT schema has one index called PK_EMP. This index is built on a single column, EMPNO.


You Might Also Like

Related Posts with Thumbnails