Indexes: View Table Indexes

Description: Examples of viewing the indexes on an Oracle table. Replace the example table names with the actual table name.

SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name='MYTABLENAME'
ORDER BY index_name, column_position
 
 
-- list all the indexes for a table:
SELECT index_name FROM user_indexes
    WHERE table_name = 'TABLE_NAME';
 
 
-- If the table is a system table then it will not be listed
-- in the user_indexes view, so instead use all_indexes:
 
    SELECT index_name FROM all_indexes
        WHERE table_name = 'TABLE_NAME';
 
 
 
-- alternate method, shows column positions:
SET linesize 110
SET verify off
col index_owner format a20
col column_name format a20
col tablespace_name format a20
break ON table_name skip 1;
 
SELECT c.index_owner, i.index_name,
DECODE(i.uniqueness, 'UNIQUE', 'YES', 'NO') UNIQUENESS,
c.column_name, c.column_position, i.tablespace_name
FROM dba_ind_columns c, dba_indexes i
WHERE i.index_name = c.index_name
AND i.table_owner = c.table_owner
ORDER BY c.index_owner, i.index_name, c.column_position;

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.