rem -----------------------------------------------------------------------
rem Filename:   acttrx.sql
rem Purpose:    Re-build all user indexes on-line for performance reasons
rem Notes:      - Run this script from sqlplus as a DBA user.
rem Date:       10-Oct-1998
rem Author:     Frank Naude (frank@onwe.co.za)
rem -----------------------------------------------------------------------

set serveroutput on size 1000000
set line 1024 feed off trimspool on echo off
spool index_rebuild_run.sql

begin
  for c1 in (
      select i.owner, i.index_name, s.tablespace_name,
             i.initial_extent, i.next_extent, i.min_extents, i.max_extents,
             i.pct_increase, s.bytes
        from sys.dba_segments s, sys.dba_indexes i
       where s.segment_type = 'INDEX'
         and i.index_name   = s.segment_name
         and i.owner not in ('SYSTEM','SYS','DBVISION','MONITOR','TUNING') )
  loop

    if c1.tablespace_name not like '%INDX%' then  -- Make sure we use the INDEX tablespace
       c1.tablespace_name := 'INDX';
    end if;
    if c1.pct_increase not in (0, 100) then       -- Set PCTINCREASE to 0
       c1.pct_increase := 0;
    end if;
    if c1.bytes/1024/1024 > 200 then              -- Largest extent will be 200K
       c1.bytes := 200*1024*1024;
    end if;
    if c1.max_extents < 121 then                  -- Set MAXEXTENTS=121 if smaller than 121
       c1.max_extents := 121;
    end if;

    dbms_output.put_line('ALTER INDEX '||c1.owner||'.'||c1.index_name||
      ' REBUILD TABLESPACE '||c1.tablespace_name||' STORAGE ('||
      ' INITIAL '||to_char(c1.bytes)||
      ' NEXT '||to_char(c1.bytes)||
      ' PCTINCREASE '||c1.pct_increase||' MAXEXTENTS '||c1.max_extents||');');
    dbms_output.put_line('ALTER TABLESPACE INDX COALESCE;');
  end loop;
end;
/

spool off

-- Run the generated script
set echo on time on timing on feed on
spool index_rebuild
@@index_rebuild_run
spool off

! rm index_rebuild_run.sql

exit