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:\rkupcuna05_ora\OracleDBA\SQL\bld_ndx3.sql              */
/*                                                  */ 
/*       >> For a specific table name <<            */
/*                                                  */
/* NOTE: will build all non-sys/system tables       */
/*                                                  */
/* ************************************************ */

spool bld_ndx3a.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
        table_name = upper('&&TABLE_NAME')
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
        table_name = upper('&&TABLE_NAME')
  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
            table_name = upper('&&TABLE_NAME')     AND
            all_ind_columns.index_name in
    		(select index_name 
    		   from all_indexes
	           where table_owner NOT in ('SYS', 'SYSTEM') and
                         table_name = upper('&&TABLE_NAME'))
   UNION
     select '  )' 					command, 
            3 						dummy, 
            index_name, 
            0 						dummy2
       from all_ind_columns
      where table_owner NOT in ('SYS', 'SYSTEM') and
            table_name = upper('&&TABLE_NAME')     AND
            all_ind_columns.index_name in
    		(select index_name 
    		   from all_indexes
	           where table_owner NOT in ('SYS', 'SYSTEM') and
                         table_name = upper('&&TABLE_NAME'))
  UNION
      select '   pctfree  '||A.pct_free||'     
   initrans '||A.ini_trans||'     
   maxtrans '||A.max_trans||'
   PARALLEL (DEGREE 2)
   UNRECOVERABLE
   storage (initial     '||decode(B.bytes,16384,'8K',B.bytes)||'
            next        '||decode(A.next_extent,16384,'8K',A.next_extent)||'
            minextents  '||A.min_extents||'
            maxextents  '||decode(A.max_extents,999,500,A.max_extents)||'
            pctincrease '||0||'
            freelists   '||A.freelists||'
           ) tablespace '||A.tablespace_name||';' 	command,
             4 						dummy,
             A.index_name                               dummy1, 
             0 						dummy2
       from all_indexes  A,
            dba_segments B
      where A.table_owner NOT in ('SYS', 'SYSTEM') AND
            B.segment_name = A.index_name          and
            B.owner        = A.owner               and
            B.segment_type = 'INDEX'               and
            A.table_name = upper('&&TABLE_NAME')
order by 3,2,4;
       
spool off;
        
undefine TABLE_NAME;
set heading on;
set pagesize 100;
set termout on;

commit;










