Retrieving Information on Indexes

user_indexes view has information on your indexes. Some columns from user_indexes view:

Column	Type	Description
index_name	VARCHAR2(30)	Name of the index
table_owner	VARCHAR2(30)	The user who owns the table
table_name	VARCHAR2(30)	The name of the table on which the index was created
uniqueness	VARCHAR2(9)	Indicates whether the index is unique (UNIQUE or NONUNIQUE)
status	VARCHAR2(8)	Indicates whether the index is valid (VALID or INVALID)

all_indexes view has information on all the indexes.


SQL> SELECT index_name, table_name, uniqueness, status
  2  FROM user_indexes
  3  WHERE table_name IN ('EMP')
  4  ORDER BY index_name;

INDEX_NAME                     TABLE_NAME                     UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------
INDEX_EMP                      EMP                            NONUNIQUE VALID

SQL>

Retrieving Information on the Indexes on a Column

The user_ind_columns view has information on the indexes on a column. Some of the columns in user_ind_columns.

Column	Type	Description
index_name	VARCHAR2(30)	Name of the index
table_name	VARCHAR2(30)	Name of the table
column_name	VARCHAR2(4000)	Name of the indexed column

The all_ind_columns view has information on all the indexes you have access to. The following query retrieves some of the columns from user_ind_columns for the customers and employees tables:


SQL> SELECT index_name, table_name, column_name
  2  FROM user_ind_columns
  3  WHERE table_name IN ('EMP')
  4  ORDER BY index_name;

INDEX_NAME                     TABLE_NAME     COLUMN_NAME
------------------------------ ----------------------------
INDEX_EMP                      EMP            ENAME
INDEX_EMP                      EMP            EMPNO


SQL>

Home »
Oracle »
Table »

Index: