Skip to content
Oracle DBA Scripts & Database Utilities

Index Storage Parameters (dindex.sql)

What This Script Does

Section titled “What This Script Does”

Displays comprehensive index storage parameters including space allocation, statistics, and physical characteristics.

The Script

Section titled “The Script”
rem  dindex.sql
rem
set lines 500
rem
ttitle 'Index Storage Parameters'
rem
col table_owner format a10 heading 'OWNER' trunc
col table_name format a18 heading 'TABLE NAME' trunc
col index_name format a12 heading 'INDEX NAME' trunc
col index_type format a7 heading 'TYPE' trunc
col partition_name format a12 heading 'PARTITION' trunc
col partition_position form 999 heading 'POS' trunc
col high_value format a9 heading 'HIGH|VALUE' trunc
col tablespace_name format a14 heading 'TABLESPACE' trunc
col initial_extent format 9,999,999 heading 'INITIAL|EXTENT|(K)' trunc
col next_extent format 9,999,999 heading 'NEXT|EXTENT|(K)' trunc
col pct_increase format 999 heading 'PCT|INC' trunc
col min_extents format 99 heading 'MIN|EXT' trunc
col max_extents format 999 heading 'MAX|EXT' trunc
col LAST_ANALYZED format a9 heading 'LAST|ANALYZED' trunc
col SAMPLE_SIZE format 999,999 heading 'SAMPLE|SIZE'
col NUM_ROWS format 999,999,999 HEADING 'NUM|ROWS'
col BLEVEL format 99
COL LEVELS FORMAT 99 HEADING 'NUM|LEVELS'
col DISTINCT_KEYS format 9,999,999 heading 'DISTINCT|KEYS' trunc
col LEAF_BLOCKS format 99,999 heading 'LEAF|BLKS'
col AVG_ROW_LEN format 9,999 heading 'AVG|ROW|LEN'
col AVG_LEAF_BLOCKS_PER_KEY format 99,999 heading 'AVG|LEAF|BLOCKS|PER KEY'
col AVG_DATA_BLOCKS_PER_KEY format 9,999 heading 'AVG|DATA|BLOCKS|PER KEY'
col CLUSTERING_FACTOR format 9,999,999 heading 'CLUSTERING|FACTOR'
col NUM_FREELIST_BLOCKS format 99,999 heading 'NUM|FREELIST|BLOCKS'
col logging format a3 heading 'LOG'
col status format a12 heading 'STATUS' trunc
col PARTITIONED format a12 heading 'PARTITIONED' trunc
col ini_trans format 999 heading 'INI|TRAN'
col max_trans format 999 heading 'MAX|TRAN'
col freelists format 999 heading 'FREE|LISTS'
rem
rem
select table_owner,
       decode( table_type, 'TABLE', null, 'CLUSTER', 'C', '?' ) table_type,
       table_name,
       index_type,
       status,
       decode( uniqueness, 'UNIQUE', 'U', null ) u,
       index_name,
  LAST_ANALYZED,
  NUM_ROWS,
       initial_extent/1024 initial_extent,
--       next_extent/1024 next_extent, pct_increase,
       degree,
       PARTITIONED    ,(BLEVEL + 1) LEVELS,LEAF_BLOCKS  ,      min_extents, max_extents,          pct_free,
       ini_trans,
tablespace_name
--       freelists
  from sys.dba_indexes
 where table_owner like nvl(upper('&owner'),'%')
   and table_name like nvl(upper('&table'),'%')
   and index_name like nvl(upper('&index'),'%')
 order by table_owner, table_name, index_name;
rem

Usage

Section titled “Usage”
-- Basic usage
@dindex.sql


-- When prompted, enter:
-- owner: Schema name (use % for all)
-- table: Table name (use % for all)
-- index: Index name (use % for all)

Parameters

Section titled “Parameters”

The script prompts for:

  • &owner - Schema name (use % for all)
  • &table - Table name (use % for all)
  • &index - Index name (use % for all)

Required Privileges

Section titled “Required Privileges”
SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_INDEXES

Sample Output

Section titled “Sample Output”
                                           Index Storage Parameters


OWNER      TABLE NAME         TYPE    STATUS       U INDEX NAME   LAST     NUM      INITIAL DEGREE PARTITIONED NUM     LEAF MIN MAX          PCT INI TABLESPACE
                                                    ANALYZED      ROWS    EXTENT         LEVELS  BLKS EXT EXT         FREE TRAN
---------- ------------------ ------- ------------ - ---------- --------- --------- ---------- --- ---------- --- ---- --- ---- ---------- ------- ----------
HR         EMPLOYEES          NORMAL  VALID        U EMP_EMAIL_UK 30-MAY-25   1,025     1,024          1    2      45   1  121          10     2 USERS
           EMPLOYEES          NORMAL  VALID          EMP_NAME_IDX  30-MAY-25   1,025       512          1    2      85   1  121          10     2 USERS
           EMPLOYEES          NORMAL  VALID          EMP_DEPT_IDX  30-MAY-25   1,025       512          1    1      15   1  121          10     2 USERS

Key Output Columns

Section titled “Key Output Columns”
  • OWNER: Schema that owns the index
  • TABLE NAME: Table the index belongs to
  • TYPE: Index type (NORMAL, BITMAP, FUNCTION-BASED, etc.)
  • STATUS: Index status (VALID, INVALID, UNUSABLE)
  • U: Uniqueness (U=UNIQUE, blank=NON-UNIQUE)
  • INDEX NAME: Name of the index
  • LAST ANALYZED: When statistics were last gathered
  • NUM ROWS: Number of rows in the indexed table
  • INITIAL EXTENT: Initial extent size in KB
  • DEGREE: Parallel degree setting
  • PARTITIONED: Whether the index is partitioned
  • NUM LEVELS: Height of the index (B-tree levels)
  • LEAF BLKS: Number of leaf blocks in the index
  • MIN/MAX EXT: Minimum and maximum extents
  • PCT FREE: Percentage of free space reserved in blocks
  • INI TRAN: Initial transaction slots
  • TABLESPACE: Tablespace where the index is stored

Common Use Cases

Section titled “Common Use Cases”

Storage Analysis

-- Review index space usage
@dindex.sql
-- Enter: HR for owner, % for table and index

Performance Tuning

-- Analyze specific index structure
@dindex.sql
-- Enter: % for owner/table, EMP_NAME_IDX for index
Section titled “Related Scripts”