REM  #*****************************************************
REM  #File Name: get_indexes.sql
REM  #
REM  #Purpose:   Script that produces index creation script
REM  #           Based on User Schema (user name specified).
REM  #           Input Value 1:  The name of the user which to
REM  #                           dump an index creation script.
REM  #
REM  #           This is script is useful for cases where
REM  #           Reverse Engineering is required. The resulting
REM  #           SQL is sent to an output file: 
REM  #           
REM  #                        ind_<SCHEMA_NAME>.lst
REM  #
REM  #*****************************************************


set arraysize 1
set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 79
define 1 = &&SCHEMA_NAME
spool ind_&&SCHEMA_NAME
set termout off
col y noprint
col x noprint
col z noprint
select  'rem   ****    Create Index DDL for '||chr(10)||
	'rem   ****    '||username||''''||'s tables'||chr(10)||chr(10)
from    dba_users 
where   username      = upper ('&&1')
/
select 	table_name z,
	index_name y,
	-1 x, 
     	'create ' || rtrim(decode(uniqueness,'UNIQUE','UNIQUE',null))
        || ' index ' ||
      	rtrim(index_name) 
from 	dba_indexes
where 	table_owner = upper('&&1')
union
select 	table_name z,
	index_name y,
	0 x, 
        'on ' ||
      	rtrim(table_name) ||
      	'('
from 	dba_indexes
where 	table_owner = upper('&&1')
union
select 	table_name z,
	index_name y, 
	column_position x,
        rtrim(decode(column_position,1,null,','))||
        rtrim(column_name)
from 	dba_ind_columns
where 	table_owner = upper('&&1')
union
select 	table_name z,
	index_name y, 
	999999 x,
      	')'  || chr(10)
	||'unrecoverable ' || chr(10)
	||'STORAGE(' 				|| chr(10)
       	||'INITIAL '	 || initial_extent 	|| chr(10)
	||'NEXT ' 	 || next_extent 	|| chr(10)
        ||'MINEXTENTS ' || '1' || chr(10)
	||'MAXEXTENTS ' || max_extents 	|| chr(10) 
	||'PCTINCREASE '|| '0'  ||')'	|| chr(10) 
	||'INITRANS '   || ini_trans         || chr(10)
	||'MAXTRANS '   || max_trans         || chr(10)
	||'PCTFREE '    || '0' || chr(10)
      	||'TABLESPACE ' || tablespace_name ||chr(10)
      	||'PARALLEL (DEGREE ' || '1' || ') ' || chr(10)
       	||'/'||chr(10)||chr(10)
from 	dba_indexes
where 	table_owner = upper('&&1')
order by 1,2,3
/
spool off