DBA_INDEXES

Description for all indexes in the database

Name Datatype Length Mandatory Comments
OWNER VARCHAR2 (30) Yes Username of the owner of the index
INDEX_NAME VARCHAR2 (30) Yes Name of the index
INDEX_TYPE VARCHAR2 (27)
TABLE_OWNER VARCHAR2 (30) Yes Owner of the indexed object
TABLE_NAME VARCHAR2 (30) Yes Name of the indexed object
TABLE_TYPE VARCHAR2 (11)
Type of the indexed object
UNIQUENESS VARCHAR2 (9)
Uniqueness status of the index: "UNIQUE", "NONUNIQUE", or "BITMAP"
COMPRESSION VARCHAR2 (8)
Compression property of the index: "ENABLED", "DISABLED", or NULL
PREFIX_LENGTH NUMBER

Number of key columns in the prefix used for compression
TABLESPACE_NAME VARCHAR2 (30)
Name of the tablespace containing the index
INI_TRANS NUMBER

Initial number of transactions
MAX_TRANS NUMBER

Maximum number of transactions
INITIAL_EXTENT NUMBER

Size of the initial extent
NEXT_EXTENT NUMBER

Size of secondary extents
MIN_EXTENTS NUMBER

Minimum number of extents allowed in the segment
MAX_EXTENTS NUMBER

Maximum number of extents allowed in the segment
PCT_INCREASE NUMBER

Percentage increase in extent size
PCT_THRESHOLD NUMBER

Threshold percentage of block space allowed per index entry
INCLUDE_COLUMN NUMBER

User column-id for last column to be included in index-only table top index
FREELISTS NUMBER

Number of process freelists allocated in this segment
FREELIST_GROUPS NUMBER

Number of freelist groups allocated to this segment
PCT_FREE NUMBER

Minimum percentage of free space in a block
LOGGING VARCHAR2 (3)
Logging attribute
BLEVEL NUMBER

B-Tree level
LEAF_BLOCKS NUMBER

The number of leaf blocks in the index
DISTINCT_KEYS NUMBER

The number of distinct keys in the index
AVG_LEAF_BLOCKS_PER_KEY NUMBER

The average number of leaf blocks per key
AVG_DATA_BLOCKS_PER_KEY NUMBER

The average number of data blocks per key
CLUSTERING_FACTOR NUMBER

A measurement of the amount of (dis)order of the table this index is for
STATUS VARCHAR2 (8)
Whether non-partitioned index is in UNUSABLE state or not
NUM_ROWS NUMBER

SAMPLE_SIZE NUMBER

The sample size used in analyzing this index
LAST_ANALYZED DATE

The date of the most recent time this index was analyzed
DEGREE VARCHAR2 (40)
The number of threads per instance for scanning the partitioned index
INSTANCES VARCHAR2 (40)
The number of instances across which the partitioned index is to be scanned
PARTITIONED VARCHAR2 (3)
Is this index partitioned? YES or NO
TEMPORARY VARCHAR2 (1)
Can the current session only see data that it place in this object itself?
GENERATED VARCHAR2 (1)
Was the name of this index system generated?
SECONDARY VARCHAR2 (1)
Is the index object created as part of icreate for domain indexes?
BUFFER_POOL VARCHAR2 (7)
The default buffer pool to be used for index blocks
USER_STATS VARCHAR2 (3)
Were the statistics entered directly by the user?
DURATION VARCHAR2 (15)
If index on temporary table, then duration is sys$session or sys$transaction else NULL
PCT_DIRECT_ACCESS NUMBER

If index on IOT, then this is percentage of rows with Valid guess
ITYP_OWNER VARCHAR2 (30)
If domain index, then this is the indextype owner
ITYP_NAME VARCHAR2 (30)
If domain index, then this is the name of the associated indextype
PARAMETERS VARCHAR2 (1000)
If domain index, then this is the parameter string
GLOBAL_STATS VARCHAR2 (3)
Are the statistics calculated without merging underlying partitions?
DOMIDX_STATUS VARCHAR2 (12)
Is the indextype of the domain index valid
DOMIDX_OPSTATUS VARCHAR2 (6)
Status of the operation on the domain index
FUNCIDX_STATUS VARCHAR2 (8)
Is the Function-based Index DISABLED or ENABLED?
JOIN_INDEX VARCHAR2 (3)
Is this index a join index?

Usage:


cursor cur_index(cv_tab_name in dba_indexes.table_name%type
               , c_owner in dba_objects.owner%type)
  -- List Indexes
  is select ind.index_name
     ,      ind.index_type
     ,      ind.uniqueness
     ,      ind.tablespace_name
     ,      ind.pct_increase
     ,      ind.pct_free
     ,      ind.owner
     from dba_indexes ind
     where ind.table_name = cv_tab_name
     and   ind.table_owner = c_owner
     order by ind.uniqueness desc, ind.index_name;