USER_INDEXES

The USER_INDEXES view provides information about the indexes owned by the current user.

Column name	Data type	Description
`INDEX_NAME`	`VARCHAR2(128)`	The name of the index. Always `NOT NULL`.
`INDEX_TYPE`	`VARCHAR2(27)`	The type of the index. The possible values are: `BITMAP` `CLUSTER` `DOMAIN` `FUNCTION-BASED BITMAP` `FUNCTION-BASED DOMAIN` `FUNCTION-BASED NORMAL` `FUNCTION-BASED NORMAL/REV` `IOT - TOP` `LOB` `NORMAL` `NORMAL/REV`
`TABLE_OWNER`	`VARCHAR2(128)`	The owner of the indexed object. Always `NOT NULL`.
`TABLE_NAME`	`VARCHAR2(128)`	The name of the indexed object. Always `NOT NULL`.
`TABLE_TYPE`	`CHAR(5)`	The type of the indexed object. Always `TABLE`.
`UNIQUENESS`	`VARCHAR2(9)`	Specifies whether the index is unique (`UNIQUE`) or nonunique (`NONUNIQUE`).
`COMPRESSION`	`VARCHAR2(13)`	The compression type being used for the index. The possible values are: `ENABLED` - Prefix compression. `ADVANCED HIGH` - Advanced high compression. `ADVANCED LOW` - Advanced low compression. `DISABLED` - No compression.
`PREFIX_LENGTH`	`NUMBER`	The number of columns in the prefix of the compression key.
`TABLESPACE_NAME`	`VARCHAR2(30)`	The name of the tablespace containing the index.
`INI_TRANS`	`NUMBER`	The initial number of transactions.
`MAX_TRANS`	`NUMBER`	The maximum number of transactions.
`INITIAL_EXTENT`	`NUMBER`	The size of the initial extent.
`NEXT_EXTENT`	`NUMBER`	The size of secondary extents.
`MIN_EXTENTS`	`NUMBER`	The minimum number of extents allowed in the segment.
`MAX_EXTENTS`	`NUMBER`	The maximum number of extents allowed in the segment.
`PCT_INCREASE`	`NUMBER`	The percentage by which each subsequent extent grows compared to the previous one.
`PCT_THRESHOLD`	`NUMBER`	The percentage of block space that a single index entry is allowed to occupy.
`INCLUDE_COLUMN`	`NUMBER`	The column ID of the last column to be included in the index-organized table (IOT) index.
`FREELISTS`	`NUMBER`	The number of process freelists allocated to this segment.
`FREELIST_GROUPS`	`NUMBER`	The number of freelist groups allocated to this segment.
`PCT_FREE`	`NUMBER`	The minimum percentage of free space in a block.
`LOGGING`	`VARCHAR2(3)`	Specifies whether changes to the index are logged (`YES`) or not (`NO`).
`STATUS`	`VARCHAR2(8)`	Specifies whether a nonpartitioned index is `VALID` or `UNUSABLE`.
`NUM_ROWS`	`NUMBER`	The number of rows in the index. For bitmap indexes, this column specifies the number of distinct keys, which is the same as the value in the `DISTINCT_KEYS` column.
`SAMPLE_SIZE`	`NUMBER`	The size of the sample used to analyze the index.
`LAST_ANALYZED`	`DATE`	The date when the index was most recently analyzed.
`DEGREE`	`VARCHAR2(40)`	The number of threads per instance for scanning the index, or `DEFAULT`.
`INSTANCES`	`VARCHAR2(40)`	The number of instances across which the indexes are to be scanned, or `DEFAULT`.
`PARTITIONED`	`VARCHAR2(3)`	Specifies whether the index is partitioned (`YES`) or not (`NO`).
`TEMPORARY`	`VARCHAR2(1)`	Specifies whether the index is on a temporary table (`Y`) or not (`N`).
`GENERATED`	`VARCHAR2(1)`	Specifies whether the name of the index is system-generated (`Y`) or not (`N`).
`SECONDARY`	`VARCHAR2(1)`	Specifies whether the index is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (`Y`) or not (`N`).
`BUFFER_POOL`	`VARCHAR2(7)`	The buffer pool used for index blocks. The possible values are: `DEFAULT` `KEEP` `RECYCLE` `NULL`
`FLASH_CACHE`	`VARCHAR2(7)`	The Database Smart Flash Cache hint used for index blocks. The possible values are: `DEFAULT` `KEEP` `NONE` Applies only to the Solaris and Oracle Linux functionality.
`CELL_FLASH_CACHE`	`VARCHAR2(7)`	The cell flash cache hint used for index blocks. The possible values are: `DEFAULT` `KEEP` `NONE`
`USER_STATS`	`VARCHAR2(3)`	Specifies whether statistics were entered directly by the user (`YES`) or not (`NO`).
`DURATION`	`VARCHAR2(15)`	The duration of a temporary table. The possible values are: `SYS$SESSION` - Rows are preserved for the duration of the session. `SYS$TRANSACTION` - Rows are deleted after `COMMIT`.
`PCT_DIRECT_ACCESS`	`NUMBER`	The percentage of rows with `VALID` guess for a secondary index on an index-organized table.
`ITYP_OWNER`	`VARCHAR2(128)`	The owner of the index type for a domain index.
`ITYP_NAME`	`VARCHAR2(128)`	The name of the index type for a domain index.
`PARAMETERS`	`VARCHAR2(1000)`	The parameter string for a domain index.
`GLOBAL_STATS`	`VARCHAR2(3)`	Specifies whether statistics are gathered or incrementally maintained (`YES`) or not (`NO`).
`DOMIDX_STATUS`	`VARCHAR2(12)`	The status of a domain index. The possible values are: `NULL` - The index is not a domain index. `VALID` - The operation performed without errors. `FAILED` - The operation failed with an error.
`DOMIDX_OPSTATUS`	`VARCHAR2(6)`	The status of the operation on a domain index. The possible values are: `NULL` - The index is not a domain index. `VALID` - The index is a valid domain index. `IDXTYP_INVLD` - The Index type of the domain index is invalid.
`FUNCIDX_STATUS`	`VARCHAR2(8)`	The status of a function-based index. The possible values are: `NULL` - The index is not a function-based index. `ENABLED` - The function-based index is enabled. `DISABLED` - The function-based index is disabled.
`JOIN_INDEX`	`VARCHAR2(3)`	Specifies whether the index is a join index (`YES`) or not (`NO`).
`IOT_REDUNDANT_PKEY_ELIM`	`VARCHAR2(3)`	Specifies whether redundant primary key columns are eliminated from secondary indexes on index-organized tables (`YES`) or not (`NO`).
`DROPPED`	`VARCHAR2(3)`	Specifies whether the index has been dropped (`YES`) or not (`NO`). For partitioned tables, the value is always `NULL`.
`VISIBILITY`	`VARCHAR2(9)`	Specifies whether the index is `VISIBLE` or `INVISIBLE` to the optimizer.
`DOMIDX_MANAGEMENT`	`VARCHAR2(14)`	Specifies whether the domain index is system-managed (`SYSTEM_MANAGED`) or user-managed (`USER_MANAGED`).
`SEGMENT_CREATED`	`VARCHAR2(3)`	Specifies whether the index segment has been created (`YES`) or not (`NO`).
`ORPHANED_ENTRIES`	`VARCHAR2(3)`	Specifies whether a global index contains stale entries because of deferred index maintenance during `DROP/TRUNCATE` `PARTITION`, or `MODIFY PARTITION INDEXING OFF` operations. The possible values are: `YES` - The index contains orphaned entries. `NO` - The index does not contain orphaned entries.
`INDEXING`	`VARCHAR2(7)`	Specifies whether a global index is decoupled from the underlying table. The possible values are: `PARTIAL` - The index is partial. It follows the table's indexing property. `FULL` - The index includes all partitions of the table.
`AUTO`	`VARCHAR2(3)`	Specifies whether the index is an auto index (`YES`) or not (`NO`). This column is available starting with Oracle Database 19c.
`CONSTRAINT_INDEX`	`VARCHAR2(3)`	Specifies whether the index was created as part of a constraint (`YES`) or not (`NO`). This column is available starting with Oracle Database 19c.