Description for all indexes in the database
Columns ___________________________ OWNER Username of the owner of the index INDEX_NAME Name of the index INDEX_TYPE -- TABLE_OWNER Owner of the indexed object TABLE_NAME Name of the indexed object TABLE_TYPE Type of the indexed object UNIQUENESS Uniqueness status of the index: "UNIQUE","NONUNIQUE" or "BITMAP" COMPRESSION Compression property of the index: "ENABLED","DISABLED" or NULL PREFIX_LENGTH Number of key columns in the prefix used for compression TABLESPACE_NAME Name of the tablespace containing the index INI_TRANS Initial number of transactions MAX_TRANS Maximum number of transactions INITIAL_EXTENT Size of the initial extent NEXT_EXTENT Size of secondary extents MIN_EXTENTS Minimum number of extents allowed in the segment MAX_EXTENTS Maximum number of extents allowed in the segment PCT_INCREASE Percentage increase in extent size PCT_THRESHOLD Threshold percentage of block space allowed per index entry INCLUDE_COLUMN User column-id for last column to be included in index-only table top index FREELISTS Number of process freelists allocated in this segment FREELIST_GROUPS Number of freelist groups allocated to this segment PCT_FREE Minimum percentage of free space in a block LOGGING Logging attribute BLEVEL B-Tree level LEAF_BLOCKS The number of leaf blocks in the index DISTINCT_KEYS The number of distinct keys in the index AVG_LEAF_BLOCKS_PER_KEY The average number of leaf blocks per key AVG_DATA_BLOCKS_PER_KEY The average number of data blocks per key CLUSTERING_FACTOR A measurement of the amount of (dis)order of the table this index is for STATUS Whether non-partitioned index is in UNUSABLE state or not NUM_ROWS -- SAMPLE_SIZE The sample size used in analyzing this index LAST_ANALYZED The date of the most recent time this index was analyzed DEGREE The number of threads per instance for scanning the partitioned index INSTANCES The number of instances across which the partitioned index is to be scanned PARTITIONED Is this index partitioned? YES or NO TEMPORARY Can the current session only see data that it place in this object itself? GENERATED Was the name of this index system generated? SECONDARY Is the index object created as part of icreate for domain indexes? BUFFER_POOL The default buffer pool to be used for index blocks USER_STATS Were the statistics entered directly by the user? DURATION If index on temporary table,then duration is sys$session or sys$transaction else NULL PCT_DIRECT_ACCESS If index on IOT,then this is percentage of rows with Valid guess ITYP_OWNER If domain index,then this is the indextype owner ITYP_NAME If domain index,then this is the name of the associated indextype PARAMETERS If domain index,then this is the parameter string GLOBAL_STATS Are the statistics calculated without merging underlying partitions? DOMIDX_STATUS Is the indextype of the domain index valid DOMIDX_OPSTATUS Status of the operation on the domain index FUNCIDX_STATUS Is the Function-based Index DISABLED or ENABLED? JOIN_INDEX Is this index a join index?
Show All Indexes
set pagesize 300
COLUMN owner FORMAT A10
COLUMN index_name FORMAT A35
COLUMN tablespace_name FORMAT A15
Select owner, index_name, tablespace_name
Where owner NOT IN('SYSTEM','DBSNMP', 'ORDSYS', 'OUTLN','SYS')
and table_type = 'TABLE'
Order By owner, index_name, tablespace_name;