rem $DBA/cdindex.sql
rem
rem Creates two SQL scripts, which can be used to drop or create all
rem non-unique indexes on a given table.  Called by cdindex.
rem
rem Parameters:
rem
rem	&1 = Schema name containing the table (uppercased) specified in &2
rem	&2 = Name of table (uppercased) whose indexes are to be dropped/created
rem
rem Restrictions:
rem	1) The invoking user needs to have SELECT access to the sys.dba_indexes
rem	   and sys.dba_ind_columns views.
rem
rem Last Change 10/01/97 by Brian Lomasky
rem
set verify off
set feedback off
set echo off
set pagesize 0
set termout off
drop table indx_temp;
set termout on
create table indx_temp (	indtype VARCHAR2(1),
				lineno NUMBER,
				text VARCHAR2(80));
declare
	cursor ind_cursor is select
		uniqueness,
		upper(owner),
		upper(index_name),
		upper(table_owner),
		upper(table_name),
		ini_trans,
		max_trans,
		tablespace_name,
		initial_extent,
		next_extent,
		min_extents,
		max_extents,
		pct_increase,
		pct_free
		from sys.dba_indexes
		where table_name = '&&2' and substr(index_name, 1, 5) <> 'SYS_C'
		and table_type = 'TABLE' and owner = '&&1'
		and uniqueness != 'UNIQUE'
		order by owner, index_name;

	cursor col_cursor (c_own varchar2, c_ind varchar2) is select
		upper(column_name), column_position
		from sys.dba_ind_columns
		where upper(index_name) = c_ind and upper(index_owner) = c_own
		order by column_position;

	lv_uniqueness		sys.dba_indexes.uniqueness%TYPE;
	lv_owner		sys.dba_indexes.owner%TYPE;
	lv_index_name		sys.dba_indexes.index_name%TYPE;
	lv_towner		sys.dba_indexes.table_owner%TYPE;
	lv_table_name		sys.dba_indexes.table_name%TYPE;
	lv_ini_trans		sys.dba_indexes.ini_trans%TYPE;
	lv_max_trans		sys.dba_indexes.max_trans%TYPE;
	lv_tablespace_name	sys.dba_indexes.tablespace_name%TYPE;
	lv_initial_extent	sys.dba_indexes.initial_extent%TYPE;
	lv_next_extent		sys.dba_indexes.next_extent%TYPE;
	lv_min_extents		sys.dba_indexes.min_extents%TYPE;
	lv_max_extents		sys.dba_indexes.max_extents%TYPE;
	lv_pct_increase		sys.dba_indexes.pct_increase%TYPE;
	lv_pct_free		sys.dba_indexes.pct_free%TYPE;
	lv_column_name		sys.dba_ind_columns.column_name%TYPE;
	lv_column_position	sys.dba_ind_columns.column_position%TYPE;
	lv_lineno		number := 0;
	initial_extent_size	varchar2(16);
	next_extent_size	varchar2(16);
	a_lin			varchar2(80);

	function wri(itype in varchar2, x_lin in varchar2, x_str in varchar2,
		x_force in number) return varchar2 is
	begin
		if length(x_lin) + length(x_str) > 80
		then
			lv_lineno := lv_lineno + 1;
			insert into indx_temp values (itype, lv_lineno, x_lin);
			if x_force = 0
			then
				return x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into indx_temp values (itype, lv_lineno,
					x_str);
				return '';
			end if;
		else
			if x_force = 0
			then
				return x_lin||x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into indx_temp values (itype,
					lv_lineno, x_lin||x_str);
				return '';
			end if;
		end if;
	end wri;

begin
	a_lin := '';
	a_lin := wri('C', a_lin, 'set echo off', 1);
	a_lin := wri('C', a_lin, 'set feedback on', 1);
	a_lin := wri('C', a_lin, 'set termout on', 1);
	a_lin := wri('C', a_lin, 'set verify off', 1);
	a_lin := wri('D', a_lin, 'set echo off', 1);
	a_lin := wri('D', a_lin, 'set feedback on', 1);
	a_lin := wri('D', a_lin, 'set termout on', 1);
	a_lin := wri('D', a_lin, 'set verify off', 1);
	open ind_cursor;
	loop
		fetch ind_cursor into
			lv_uniqueness,
			lv_owner,
			lv_index_name,
			lv_towner,
			lv_table_name,
			lv_ini_trans,
			lv_max_trans,
			lv_tablespace_name,
			lv_initial_extent,
			lv_next_extent,
			lv_min_extents,
			lv_max_extents,
			lv_pct_increase,
			lv_pct_free;
		exit when ind_cursor%NOTFOUND;
		a_lin := wri('D', a_lin, 'prompt Dropping index ', 0);
		a_lin := wri('D', a_lin, lv_owner || '.' || lv_index_name, 1);
		a_lin := wri('D', a_lin, 'drop index ', 0);
		a_lin := wri('D', a_lin, lv_owner || '.' || lv_index_name, 0);
		a_lin := wri('D', a_lin, ';', 1);
		if to_char(lv_ini_trans) = '0' then
			lv_ini_trans := 1;
		end if;
		if to_char(lv_max_trans) = '0' then
			lv_max_trans := 1;
		end if;
		a_lin := wri('C', a_lin, 'prompt Creating index ', 0);
		a_lin := wri('C', a_lin, lv_owner || '.' || lv_index_name, 1);
		if lv_uniqueness = 'UNIQUE' then
			a_lin := wri('C', a_lin, 'create unique index ', 0);
		elsif lv_uniqueness = 'BITMAP' then
			a_lin := wri('C', a_lin, 'create bitmap index ', 0);
		else
			a_lin := wri('C', a_lin, 'create index ', 0);
		end if;
		a_lin := wri('C', a_lin, lv_owner || '.' || lv_index_name, 0);
		a_lin := wri('C', a_lin, ' on ', 0);
		a_lin := wri('C', a_lin, lv_towner, 0);
		a_lin := wri('C', a_lin, '.' || lv_table_name, 0);
		a_lin := wri('C', a_lin, ' (', 0);
		open col_cursor(lv_owner,lv_index_name);
		loop
			fetch col_cursor into
				lv_column_name,
				lv_column_position;
			exit when col_cursor%notfound;
			if lv_column_position <> 1 then
				a_lin := wri('C', a_lin, ',', 0);
			end if;
			a_lin := wri('C', a_lin,
				chr(34) || lv_column_name || chr(34), 0);
		end loop;
		close col_cursor;
		a_lin := wri('C', a_lin, ')', 0);
		a_lin := wri('C', a_lin, ' TABLESPACE ' || lv_tablespace_name,
			0);
		a_lin := wri('C', a_lin, ' INITRANS ' || to_char(lv_ini_trans),
			0);
		a_lin := wri('C', a_lin, ' MAXTRANS ' || to_char(lv_max_trans),
			0);
		a_lin := wri('C', a_lin, ' PCTFREE ' || to_char(lv_pct_free),
			1);
		/* Calculate extent sizes in Mbytes or Kbytes, if possible */
		if mod(lv_initial_extent, 1048576) = 0 then
			initial_extent_size :=
				to_char(lv_initial_extent / 1048576) || 'M';
		elsif mod(lv_initial_extent, 1024) = 0 then
			initial_extent_size :=
				to_char(lv_initial_extent / 1024) || 'K';
		else
			initial_extent_size := to_char(lv_initial_extent);
		end if;
		if mod(lv_next_extent, 1048576) = 0 then
			next_extent_size :=
				to_char(lv_next_extent / 1048576) || 'M';
		elsif mod(lv_next_extent, 1024) = 0 then
			next_extent_size :=
				to_char(lv_next_extent / 1024) || 'K';
		else
			next_extent_size := to_char(lv_next_extent);
		end if;
		a_lin := wri('C', a_lin, '    STORAGE (INITIAL ' ||
			initial_extent_size, 0);
		a_lin := wri('C', a_lin, ' NEXT ' || next_extent_size, 0);
		a_lin := wri('C', a_lin, ' MINEXTENTS ' ||
			to_char(lv_min_extents), 0);
		a_lin := wri('C', a_lin, ' MAXEXTENTS ' ||
			to_char(lv_max_extents), 0);
		a_lin := wri('C', a_lin, ' PCTINCREASE ' ||
			to_char(lv_pct_increase), 0);
		a_lin := wri('C', a_lin, ');', 1);
	end loop;
	close ind_cursor;
	a_lin := wri('C', a_lin, 'exit', 1);
	a_lin := wri('D', a_lin, 'exit', 1);
	commit;
exception
        when others then
                rollback;
                raise_application_error(-20000,
                        'Unexpected error on ' || lv_index_name ||
			', ' || lv_column_name || ': ' ||
                        to_char(SQLCODE) || ' - Aborting...');
end;
/
set termout off
set heading off
spool cdindext.sql
select 'spool ' || '&&2' || '_drop.sql' from dual;
select 'select text from indx_temp where indtype = ' || chr(39) || 'D' ||
	chr(39) from dual;
select 'order by lineno;' from dual;
select 'spool off' from dual;
select 'spool ' || '&&2' || '_create.sql' from dual;
select 'select text from indx_temp where indtype = ' || chr(39) || 'C' ||
	chr(39) from dual;
select 'order by lineno;' from dual;
select 'spool off' from dual;
spool off
@cdindext
drop table indx_temp;
set termout on
exit