set linesize 80;
set pagesize 10000;
set long 50;
set echo on;

/* ************************************************ */
/*                                                  */
/* Script to spool a listing of all drop and create */
/*    statements required to rebuild indexes.       */
/* Contents: Table name, col definitions and        */
/*    storage parameters.                           */
/*                                                  */
/* NOTE: will build all non-sys/system tables       */
/*                                                  */
/* ************************************************ */

set termout off;
set echo off;
set feedback off;

col dummy       noprint format a1;
col dummy2      noprint format a1;
col index_name  noprint format a1;
col command     format a80;

/* ********************************* */
/* 1. Generate drop index commands   */
/* 2. Generate create index commands */
/* ********************************* */

 select 'drop index '||owner||'.'||index_name||';'   command,
         0                                           dummy,
         index_name,
         0                                           dummy2
   from all_indexes
  where table_owner NOT in ('SYS', 'SYSTEM')
UNION
   select 'create'||decode(UNIQUENESS, 'UNIQUE', ' UNIQUE', NULL)||'
index '||owner||'.'||index_name||' on '
   ||table_owner||'.'||table_name||' ('                 command,
         1                                      dummy,
         index_name,
         0                                      dummy2
     from all_indexes
     where table_owner NOT in ('SYS', 'SYSTEM')
  UNION
     select '  '||decode(column_position,1,null,',')||column_name||' ASC' command,
            2                                      dummy,
            index_name,
            column_position                             dummy2
       from all_ind_columns
      where table_owner NOT in ('SYS', 'SYSTEM')   AND
            all_ind_columns.index_name in
                (select index_name
                   from all_indexes
                   where table_owner NOT in ('SYS', 'SYSTEM'))
   UNION
     select '  )'                                      command,
            3                                      dummy,
            index_name,
            0                                      dummy2
       from all_ind_columns
      where table_owner NOT in ('SYS', 'SYSTEM')     AND
            all_ind_columns.index_name in
                (select index_name
                   from all_indexes
                   where table_owner NOT in ('SYS', 'SYSTEM'))
  UNION
     select '   pctfree  '||pct_free||'
   initrans '||ini_trans||'
   maxtrans '||max_trans||'
   storage (initial     '||initial_extent||'
            next        '||next_extent||'
            minextents  '||min_extents||'
            maxextents  '||max_extents||'
            pctincrease '||pct_increase||'
           ) tablespace '||tablespace_name||';'     command,
             4                                      dummy,
             index_name,
             0                                      dummy2
      from all_indexes
      where table_owner NOT in ('SYS', 'SYSTEM')
order by 3,2,4;

spool bld_ndx2.rslt
/
spool off;

set heading on;
set pagesize 100;
set termout on;