rem -----------------------------------------------------------------------
rem Filename:   idxrebld.sql
rem Purpose:    Re-build all user indexes on-line 
rem Notes:      - Run this script from sqlplus as a DBA user.
rem Date:       10-Oct-1998, updated on 04-Mar-2002
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

rem -----------------------------------------------------------------------
rem Note: you need to run "ALTER INDEX ....LOGGING;" right after this 
rem       script if you have a standby database, otherwise it will be 
rem       invalidated                        - Branko Milosevic
rem -----------------------------------------------------------------------

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

declare
  INDEX_TABLESPACE_NAME constant varchar2(30) := 'INDX';   -- Set to your Index TS!!!
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 '%'||INDEX_TABLESPACE_NAME||'%' then
                                                  -- Make sure we use the right INDEX tablespace
       c1.tablespace_name := INDEX_TABLESPACE_NAME;
    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 ONLINE NOLOGGING'|| 
      ' 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 '||INDEX_TABLESPACE_NAME||' 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