REM
REM indexrpt.sql
REM ============
REM
REM This script is provided by Database Specialists, Inc. 
REM (http://www.dbspecialists.com) for individual use and not for sale. 
REM Database Specialists, Inc. does not warrant the script in any way 
REM and will not be responsible for any loss arising out of its use.
REM
REM Your feedback is welcome! Please send your comments about this script
REM to scriptfeedback@dbspecialists.com
REM
REM This script generate a report listing each index in a specified
REM tablespace, including:
REM   - index owner and name
REM   - current index size (in bytes)
REM   - current number of extents
REM   - largest extent size (in bytes)
REM   - estimated index size if the index were to be rebuilt
REM
REM The information in this report can be useful when rebuilding indexes
REM and reorging index tablespaces. 
REM
REM Run this script as a DBA user who will have sufficient privileges to
REM analyze indexes in other schemas and query the SYS.dba_% views.
REM
REM Version 03-26-1999
REM

SET VERIFY       OFF
SET FEEDBACK     OFF
SET SERVEROUTPUT ON SIZE 32767

ACCEPT ts CHAR PROMPT "Enter tablespace name: "

PROMPT
PROMPT INDEXES IN TABLESPACE &ts
PROMPT

DECLARE
  -- 
  -- Cursor to fetch all indexes in the specified tablespace.
  --
  CURSOR c_indexes IS
    SELECT   owner, index_name, pct_free
    FROM     SYS.dba_indexes
    WHERE    tablespace_name = UPPER ('&ts')
    AND      owner != 'SYS'
    ORDER BY owner, index_name;
  --
  -- Cursor to fetch statistics for a specific index. (Assumes the index
  -- has just been analyzed.)
  --
  CURSOR c_stats (cp_owner IN VARCHAR2, cp_index_name IN VARCHAR2,
                  cp_pct_free IN NUMBER) IS
    SELECT   RPAD (SUBSTR (cp_owner || '.' || cp_index_name, 1, 33), 33) ||
             TO_CHAR (SG.bytes, '9,999,999,990') || 
             TO_CHAR (SG.extents, '990') ||
             TO_CHAR (MAX (X.bytes), '9,999,999,990') ||
             TO_CHAR ((ST.used_space - ST.del_lf_rows_len) * 100 / 
                      (100 - cp_pct_free), '9,999,999,990') stats
    FROM     SYS.dba_segments SG, index_stats ST, SYS.dba_extents X
    WHERE    SG.owner = cp_owner
    AND      SG.segment_name = cp_index_name
    AND      SG.segment_type = 'INDEX'
    AND      ST.name = SG.segment_name
    AND      X.owner = SG.owner
    AND      X.segment_name = SG.segment_name
    AND      X.segment_type = SG.segment_type
    GROUP BY SG.bytes, SG.extents, ST.used_space, ST.del_lf_rows_len;
  v_cursor INTEGER;
  v_stmt   VARCHAR2(100);
  v_stats  VARCHAR2(80);
  v_dummy  INTEGER;
BEGIN
  --
  -- Output a report heading.
  --
  dbms_output.put_line ('Index Name                         Current Size ' ||
                        'Ext   Largest Ext  Rebuilt Size');
  dbms_output.put_line ('--------------------------------- ------------- ' ||
                        '--- ------------- -------------');
  --
  -- Iterate through every index in the specified tablespace.
  --
  FOR r IN c_indexes LOOP
    --
    -- Use dynamic SQL to issue an ANALYZE INDEX statement to collect detailed
    -- statistics about each index.
    --
    v_cursor := dbms_sql.open_cursor;
    v_stmt := 'ANALYZE INDEX "' || r.owner || '"."' || r.index_name ||
              '" VALIDATE STRUCTURE';
    dbms_sql.parse (v_cursor, v_stmt, dbms_sql.native);
    v_dummy := dbms_sql.execute (v_cursor);
    dbms_sql.close_cursor (v_cursor);
    --
    -- Fetch the statistics and output them.
    --
    OPEN c_stats (r.owner, r.index_name, r.pct_free);
    FETCH c_stats INTO v_stats;
    dbms_output.put_line (v_stats);
    CLOSE c_stats;
  END LOOP;
END;
/