DBA_INDEXES

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
From dba_indexes
Where owner NOT IN('SYSTEM','DBSNMP', 'ORDSYS', 'OUTLN','SYS')
and table_type = 'TABLE'
Order By owner, index_name, tablespace_name;