rebuild index estimate script

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;
/