#! /bin/ksh

export PATH=$PATH:$ORACLE_PATH/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/dt/lib:/usr/openwin/lib:/usr/lib
export rptdate=`date +%y%m%d%H%M%S`;


echo "Please enter the Oracle Instance: "
read instance
echo " "

echo "Please enter the user: " 
read user
echo " "

stty -echo
echo "Please enter the password for $user (will not display): " 
read password
stty echo
echo " "

export RPT=/tmp/${instance}_${user}_${rptdate}_rpt

echo " "
echo " "
echo "Report will be in $RPT"
echo " "
echo " "
echo "Do you want to continue? (y/n) [n]: \c "
read continue

if [ "$continue" != y ]
then
        echo "exiting"
        exit 0
fi

${ORACLE_HOME}/bin/sqlplus ${user}/${password}@$instance <<EOF > $RPT 2>&1

drop table i_temp
/

create table i_temp  
  (lineno NUMBER, id_owner VARCHAR2(30), id_name VARCHAR2(30),text VARCHAR2(800)) 
/ 

create or replace procedure write_out(p_line INTEGER, p_owner varchar2, p_name VARCHAR2, p_string VARCHAR2) 
is
begin
insert into i_temp (lineno,id_owner, id_name,text)
values (p_line,p_owner,p_name,p_string);
commit;
end write_out;
/

CREATE OR REPLACE PROCEDURE create_part_script
IS
CURSOR part_cursor
IS
select distinct
e.owner,
e.index_name,
e.table_owner,
e.table_name,
e.uniqueness,
e.tablespace_name,
part.ini_trans,
part.max_trans,
part.initial_extent,
part.next_extent,
part.min_extent,
part.max_extent,
part.pct_increase,
part.pct_free
from all_indexes e, 
all_ind_partitions part
where e.INDEX_NAME = part.INDEX_NAME and
e.table_name like '%EVENT%'; 
--e.table_name = 'EVENT_T'; 

CURSOR col_cursor (i_own VARCHAR2, c_ind VARCHAR2, c_tab VARCHAR2)
IS
select column_name
from all_ind_columns
where index_owner = i_own
and index_name = c_ind
and table_name = c_tab
order by column_position;

lv_index_owner all_indexes.owner%TYPE;
lv_index_name all_indexes.index_name%TYPE;
lv_table_owner all_indexes.table_owner%TYPE;
lv_table_name all_indexes.table_name%TYPE;
lv_uniqueness all_indexes.uniqueness%TYPE;
lv_tablespace_name all_indexes.tablespace_name%TYPE;

lv_ini_trans all_ind_partitions.ini_trans%TYPE;
lv_max_trans all_ind_partitions.max_trans%TYPE;
lv_initial_extent all_ind_partitions.initial_extent%TYPE;
lv_next_extent all_ind_partitions.next_extent%TYPE;
lv_min_extents all_ind_partitions.min_extent%TYPE;
lv_max_extents all_ind_partitions.max_extent%TYPE;
lv_pct_increase all_ind_partitions.pct_increase%TYPE;
lv_pct_free all_ind_partitions.pct_free%TYPE;
lv_column_name all_ind_columns.column_name%TYPE;
lv_first_rec BOOLEAN;
lv_string VARCHAR2(800);
lv_lineno number := 0;

BEGIN

execute immediate ( 'truncate table i_temp ');

OPEN part_cursor;
LOOP
FETCH part_cursor INTO lv_index_owner,
lv_index_name,
lv_table_owner,
lv_table_name,
lv_uniqueness,
lv_tablespace_name,
lv_ini_trans,
lv_max_trans,
lv_initial_extent,
lv_next_extent,
lv_min_extents,
lv_max_extents,
lv_pct_increase,
lv_pct_free;
EXIT WHEN part_cursor%NOTFOUND;
lv_lineno := 1;
lv_first_rec := TRUE;

if ( lv_uniqueness = 'UNIQUE' )
then
lv_string:= 'CREATE UNIQUE INDEX ' || lv_index_owner || '.' || lv_index_name;
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
else
lv_string:= 'CREATE INDEX ' || lv_index_owner || '.' || lv_index_name;
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
end if;

OPEN col_cursor(lv_index_owner,lv_index_name,lv_table_name);
LOOP
FETCH col_cursor INTO lv_column_name;
EXIT WHEN col_cursor%NOTFOUND;
if (lv_first_rec) then
lv_string := ' ON '|| lower(lv_table_owner) || '.' ||
lower(lv_table_name)||' (';
lv_first_rec := FALSE;
else
lv_string := lv_string || ',';
end if;
lv_string := lv_string || lower(lv_column_name);
END LOOP;
CLOSE col_cursor;

lv_string := lv_string || ')';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := null;
lv_string := 'PCTFREE ' || to_char(lv_pct_free);
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'INITRANS ' || to_char(lv_ini_trans) ||
' MAXTRANS ' || to_char(lv_max_trans);
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'TABLESPACE ' || lv_tablespace_name || ' STORAGE (';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'INITIAL ' || to_char(lv_initial_extent) ||
' NEXT ' || to_char(lv_next_extent);
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := 'MINEXTENTS ' || to_char(lv_min_extents) ||
' MAXEXTENTS ' || to_char(lv_max_extents) ||
' PCTINCREASE ' || to_char(lv_pct_increase) || ')';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_string := '/';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
lv_lineno := lv_lineno + 1;
lv_lineno := lv_lineno + 1;
lv_string:=' ';
write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);

END LOOP;
CLOSE part_cursor;

END create_part_script;

/

execute create_part_script

/
@make_index

# create_part_index.sql
EOF