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;