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

/* ************************************************ */
/*                                                  */
/* script to spool a listing of all drop and create */
/*    statements required to rebuild indexes.       */
/* Contents: Table name, col definitions and        */
/*    storage parameters.                           */
/*                                                  */
/*         @c:\oracle\sql\bld_ndx5.sql              */
/*                                                  */ 
/*       >> For a specific table name <<            */
/*                                                  */
/* NOTE: will build all indexs like '%NDX%'         */
/*                                                  */
/* ************************************************ */

spool my_indexes_ddl.rslt

/* 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 table commands   */
/* 2. Generate create table commands */
/* ********************************* */

 select 'drop index '||owner||'.'||index_name||';' 	command, 
         0 						dummy, 
         index_name, 
         0 						dummy2 
   from all_indexes
  where table_owner NOT in ('SYS', 'SYSTEM') and
        index_name like '%_NDX%'
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') and
        index_name like '%_NDX%'
  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
            index_name like '%_NDX%'               AND
            all_ind_columns.index_name in
    		(select index_name 
    		   from all_indexes
	           where table_owner NOT in ('SYS', 'SYSTEM') and
                         index_name like '%_NDX%')
   UNION
     select '  )' 					command, 
            3 						dummy, 
            index_name, 
            0 						dummy2
       from all_ind_columns
      where table_owner NOT in ('SYS', 'SYSTEM') and
            index_name like '%_NDX%'             AND
            all_ind_columns.index_name in
    		(select index_name 
    		   from all_indexes
	           where table_owner NOT in ('SYS', 'SYSTEM') and
                         index_name like '%_NDX%')
  UNION
     select '   pctfree  '||pct_free||'     
   initrans '||ini_trans||'     
   maxtrans '||max_trans||'
   UNRECOVERABLE
   storage (initial     '||initial_extent||'
            next        '||next_extent||'
            minextents  '||min_extents||'
            maxextents  '||max_extents||'
            pctincrease '||pct_increase||'
            freelists   '||freelists||'
           ) tablespace '||tablespace_name||';' 	command,
             4 						dummy, 
             index_name, 
             0 						dummy2
      from all_indexes 
      where table_owner NOT in ('SYS', 'SYSTEM') and
        index_name like '%_NDX%'
order by 3,2,4;
       
spool off;
        
set heading on;
set pagesize 100;
set termout on;

commit;










