DBA_IND_COLUMNS

COLUMNs comprising INDEXes on all TABLEs and CLUSTERs

Name Datatype Length Mandatory Comments
INDEX_OWNER VARCHAR2 (30) Yes Index owner
INDEX_NAME VARCHAR2 (30) Yes Index name
TABLE_OWNER VARCHAR2 (30) Yes Table or cluster owner
TABLE_NAME VARCHAR2 (30) Yes Table or cluster name
COLUMN_NAME VARCHAR2 (4000)   Column name or attribute of object column
COLUMN_POSITION NUMBER   Yes Position of column or attribute within index
COLUMN_LENGTH NUMBER   Yes Maximum length of the column or attribute, in bytes
CHAR_LENGTH NUMBER     Maximum length of the column or attribute, in characters
DESCEND VARCHAR2 (4)   DESC if this column is sorted in descending order on disk, otherwise ASC

Usage:


cursor cur_index_col(cv_index_name in dba_ind_columns.index_name%type
                   , c_owner in dba_objects.owner%type)
  -- List index columns
  is select ic.column_name
     ,      ic.column_length
     from dba_ind_columns ic
     where ic.index_name = cv_index_name
     and   ic.index_owner = c_owner
     order by ic.column_position;

-- List all indexes for a table
col column_name format a60
break on index_name skip 1 
select index_name, column_name 
from user_ind_columns 
where table_name = upper('&table') 
order by index_name, column_position, column_name;