REM ========================================================================
REM UNDO PACKAGE
REM Copyright 2000 by Yuval Oren (yuval@bluecast.com).
REM
REM This software is released under the terms of the GNU Lesser General
REM Public License, available at http://www.gnu.org/licenses/lgpl.html.
REM
REM PURPOSE:	UNDO package for uncreating and recreating objects.
REM
REM USAGE:	
REM		undo.uncreate('index','[index_name]'); 
REM				Stores the definition of an index and then drops it.
REM
REM		undo.recreate('index','[index_name]');
REM				Re-creates an index previously dropped with "uncreate".
REM
REM		undo.recreate('index','[index_name]','[extra options]');
REM				Re-creates an index with a specified clause. Usually used
REM            to create an index in parallel:
REM              undo.recreate('index','[index_name]','parallel 4');
REM
REM      undo.unindex('[table_name]','[index_type]');
REM      undo.reindex('[table_name]','[index_type]');
REM      undo.reindex('[table_name]','[index_type]','[extra options]');
REM 				Drops or recreates all indexes of a particular type from
REM            a table. Usually used to drop bitmap indexes:
REM              undo.unindex('[table_name]','bitmap');
REM
REM DESCRIPTION:
REM
REM The UNDO package contains procedures for dropping objects and then
REM recreating them. 
REM
REM The UNCREATE procedure records all the information
REM for the object and then drops it. It stores the SQL statement that will
REM recreate the object in the UNDO_STORAGE table. 
REM
REM The RECREATE procedure executes the statement that UNCREATE constructed
REM and then deletes the record from the UNDO_STORAGE table.
REM
REM This package can be expanded to handle many different object types. For
REM now, however, only indexes are supported.
REM
REM
REM TO CREATE THIS PACKAGE: 
REM
REM This package must be created by a user that has access to the all_*
REM views. The user should also have full privileges to any objects that
REM will need to be uncreated and recreated. The SYS user may be appropriate.
REM
REM Security should not be a major concern, because this package only allows
REM users to uncreate and recreate objects owned by them.
REM
REM HANDLING ERRORS:
REM
REM This package can raise two exceptions:
REM     no_data_found
REM		The object you're trying to recreate was not uncreated 
REM		(at least not by the same user)
REM 	undo.not_supported
REM	 	The object type you're trying to uncreate or recreate
REM		is not supported.
REM
REM HISTORY:
REM
REM $Log: undo.sql,v $
REM Revision 1.12  2000/02/14 21:01:57  yoren
REM Updated to work with all_* views instead of dba_*
REM
REM Revision 1.11  1999/06/09 18:36:28  yoren
REM Added option clause version of rebitmap()
REM
REM Revision 1.10  1999/06/03 18:52:39  yoren
REM Fixed to work with both Oracle 7 and 8.
REM
REM Revision 1.5  1999/05/07 05:37:11  yoren
REM Added unbitmap() and rebitmap()
REM
REM Revision 1.4  1999/04/29 17:52:29  yoren
REM Removed debug messages.
REM
REM Revision 1.3  1999/04/08 22:26:31  yoren
REM Added limited support for Oracle 8.
REM
REM Revision 1.2  1999/03/01 19:36:50  yoren
REM Raise exceptions when we have errors.
REM
REM Revision 1.1  1999/01/19 19:18:44  yoren
REM Initial revision
REM
REM
REM
REM
REM ========================================================================

CREATE TABLE undo_storage (
user_name		varchar2(30)		default user not null,
undo_date		date			default sysdate not null,
object_type		varchar2(12) 		not null,
object_subtype		varchar2(12)		, -- UNIQUE,BITMAP,etc
object_parent 		varchar2(30)		not null, -- table name
object_name		varchar2(30)		not null,
redo_statement 		long			not null);


-- Create the views below if you're using Oracle 7
/*
CREATE OR REPLACE VIEW undo_all_indexes as
select
 	OWNER,
	INDEX_NAME,
 	TABLE_OWNER,
 	TABLE_NAME,
 	TABLE_TYPE,
 	decode(UNIQUENESS,'BITMAP','NONUNIQUE',UNIQUENESS) UNIQUENESS,
	decode(UNIQUENESS,'BITMAP','BITMAP','NORMAL') INDEX_TYPE,
	'NO' PARTITIONED,
 	TABLESPACE_NAME,
 	INI_TRANS,
 	MAX_TRANS,
 	INITIAL_EXTENT,
 	NEXT_EXTENT,
 	MIN_EXTENTS,
 	MAX_EXTENTS,
 	PCT_INCREASE,
 	FREELISTS,
 	FREELIST_GROUPS,
 	PCT_FREE,
 	BLEVEL,
	LEAF_BLOCKS,
 	DISTINCT_KEYS,
 	AVG_LEAF_BLOCKS_PER_KEY,
 	AVG_DATA_BLOCKS_PER_KEY,
 	CLUSTERING_FACTOR,
 	STATUS,
        null ITYP_OWNER,
        null ITYP_NAME,
        null PARAMETERS
from sys.all_indexes;

CREATE OR REPLACE VIEW undo_all_ind_partitions as
select
 'X' INDEX_OWNER,
 'X' INDEX_NAME,
 'X' PARTITION_NAME,
 'X' HIGH_VALUE,
  0  HIGH_VALUE_LENGTH,	
  0  PARTITION_POSITION,
 'X' STATUS,
 'X' TABLESPACE_NAME,
  0  PCT_FREE,
  0  INI_TRANS,
  0  MAX_TRANS,
  0  INITIAL_EXTENT,
  0  NEXT_EXTENT,
  0  MIN_EXTENT,
  0  MAX_EXTENT,
  0  PCT_INCREASE,
  0  FREELISTS,
  0  FREELIST_GROUPS
from dual where 1 = 0;

*/


-- Use these instead if you're using Oracle 8i
CREATE SYNONYM undo_all_indexes for sys.all_indexes;
CREATE SYNONYM undo_all_ind_partitions for sys.all_ind_partitions;


	
-- Procedures for uncreating and recreating indexes
CREATE OR REPLACE PACKAGE undo as

	-- Drop an object but keep the information from the DB dictionary
	-- so that it can be recreated with the same parameters.
	-- Currently, only object type 'index' is supported.
	PROCEDURE uncreate(
		v_object_type in all_objects.object_type%type,
		v_object_name in all_objects.object_name%type
			  );

	-- Disable an object instead of dropping it.
	-- Index disabling is only supported in Oracle 8.
	PROCEDURE disable (
		v_object_type in all_objects.object_type%type,
		v_object_name in all_objects.object_name%type
			  );

	-- "Uncreate" all indexes of a certain type on a table
	PROCEDURE unindex(
		v_table_name in all_objects.object_name%type,
		v_index_type in undo_all_indexes.index_type%type
			  );

	-- Recreate an object with the same options it had 
	-- before dropped. All parameters are case insensitive.
	PROCEDURE recreate(
		v_object_type in all_objects.object_type%type,
		v_object_name in all_objects.object_name%type
			  );	

	-- Recreate an object with the same options it had before dropped.
	-- This version allows you to specify any string that will be
	-- appended to the sql statement used to recreate the object. One 
	-- example string might be 'unrecoverable'.
	PROCEDURE recreate(
		v_object_type in all_objects.object_type%type,
		v_object_name in all_objects.object_name%type,
		v_option_clause in varchar2
			  );

	-- Re-enable an object with the same options it had 
	-- before dropped. All parameters are case insensitive.
	PROCEDURE enable(
		v_object_type in all_objects.object_type%type,
		v_object_name in all_objects.object_name%type
			  );	

	-- Re-enable an object with the same options it had 
	-- before dropped. All parameters are case insensitive.
	PROCEDURE enable(
		v_object_type in all_objects.object_type%type,
		v_object_name in all_objects.object_name%type,
		v_option_clause in varchar2
			  );	


	-- "Recreate" all indexes of a type on a table
	PROCEDURE reindex(
		v_table_name in all_objects.object_name%type,
		v_index_type in undo_all_indexes.index_type%type
			  );

	-- "Recreate" all bitmap indexes on a table with an option clause
	PROCEDURE reindex(
		v_table_name in all_objects.object_name%type,
		v_index_type in undo_all_indexes.index_type%type,
		v_option_clause in varchar2
			  );


	-- Custom exceptions that this package raises.
	not_supported      	exception;

END undo;
/
show errors


CREATE OR REPLACE PACKAGE BODY undo as
	--------------------------------------------------------------
	-- Prototypes for procedures to handle specific types       --
	--------------------------------------------------------------
	PROCEDURE uncreate_index(
		v_index in undo_all_indexes.index_name%type
				);

	PROCEDURE disable_index(
		v_index in undo_all_indexes.index_name%type
				);


	--------------------------------------------------------------
	-- Get the version number of Oracle from the                -- 
	-- product_component_version view. Removes all but the 1st  --
	-- dot to make it a number.                                 --
	-- Not used currently.					    --	
	--------------------------------------------------------------
	FUNCTION get_oracle_version 
	  return number is v_version number;
	BEGIN
                select max(to_number(
                        -- Major version dot
                        substr(version,1,instr(version,'.'))
                        -- Minor versions concatenated
                        || replace(substr(version,instr(version,'.')+1),
                                     '.','')))
		into v_version
                from product_component_version
                where upper(product) like 'ORACLE%';
	
		return(v_version);
	END;



	----------------------------------------------------------------
	-- Generic UNCREATE procedure. Dispatches to the appropriate  --
	-- procedure for the specified object type.                   --
	----------------------------------------------------------------
	PROCEDURE uncreate(
		v_object_type in all_objects.object_type%type,
		v_object_name in all_objects.object_name%type
			  ) is
	BEGIN	
		if upper(v_object_type) = 'INDEX' then
		    uncreate_index(v_object_name);
		else
		    raise not_supported;
		end if;
	END;



	----------------------------------------------------------------
	-- Generic DISABLE procedure. Dispatch to the appropriate    --
	-- procedure for the specified object type.                   --
	----------------------------------------------------------------
	PROCEDURE disable (
		v_object_type in all_objects.object_type%type,
		v_object_name in all_objects.object_name%type
			  ) is
	BEGIN	
		if (upper(v_object_type) = 'INDEX' 
			and get_oracle_version >= 8) then
		    disable_index(v_object_name);
		else
		    raise not_supported;
		end if;
	END;


	----------------------------------------------------------------
	-- UNCREATE procedure for indexes,                            --
	----------------------------------------------------------------
	PROCEDURE uncreate_index(
		v_index in undo_all_indexes.index_name%type
				) is

		v_cursor_id	integer := null;
		v_create_stmt	long := null;
		v_columns	long := null;
	  	v_index_type 	varchar2(7) := null;
	  	v_table		undo_all_indexes.table_name%type := null;
		v_ini_trans	undo_all_indexes.ini_trans%type := null;
		v_max_trans	undo_all_indexes.max_trans%type := null;
		v_min_extents	undo_all_indexes.min_extents%type := null;
		v_max_extents	undo_all_indexes.max_extents%type := null;
	  	v_initial_ext 	undo_all_indexes.initial_extent%type := null;
	  	v_next_ext 	undo_all_indexes.next_extent%type := null;
		v_pct_increase	undo_all_indexes.pct_increase%type := null;
		v_pct_free	undo_all_indexes.pct_free%type := null;
		v_tablespace	undo_all_indexes.tablespace_name%type := null;
		v_cluster	varchar2(8) := null;
		v_partitioned	undo_all_indexes.partitioned%type := null;
		v_ityp_owner	undo_all_indexes.ityp_owner%type := null;
		v_ityp_name	undo_all_indexes.ityp_name%type := null;
		v_ityp_parameters undo_all_indexes.parameters%type := null;				 


		-- Cursor for looping through the columns of an index
		cursor c_ind_columns(
			v_index_name in all_ind_columns.index_name%type,
			v_table in all_ind_columns.table_name%type
				    ) is
		    select column_name
		    from all_ind_columns
		    where index_name = upper(v_index_name)
		    and index_owner = user
		    and table_name = upper(v_table)
		    order by column_position;


		-- Cursor for looping through partitions of an index	
		cursor c_ind_partitions(
		    v_index_name in undo_all_ind_partitions.index_name%type)
		is
	    	    select 	
			partition_name,
			partition_position,
			ini_trans,
			max_trans,
			min_extent,
			max_extent,
			initial_extent, 
			next_extent,
			pct_increase, 
			pct_free, 
			tablespace_name
	    	    from 	
			undo_all_ind_partitions
	    	    where 	
			index_owner = user
			and index_name = upper(v_index_name)
		    order by
			partition_position;


	BEGIN

	    savepoint undo_savept;

	    -- Get info about this index
	    select 	
		index_type,
	     	table_name,
		ini_trans,
		max_trans,
		min_extents,
		max_extents,
		initial_extent, 
		next_extent,
		pct_increase, 
		pct_free, 
		tablespace_name,
		decode(table_type,'TABLE','','CLUSTER'),
		partitioned,
		ityp_owner,
		ityp_name,
		replace(parameters,'replace','')
	    into	
		v_index_type,
		v_table,
		v_ini_trans,
		v_max_trans,
		v_min_extents,
		v_max_extents,
		v_initial_ext,
		v_next_ext,
		v_pct_increase,
		v_pct_free,
		v_tablespace,
		v_cluster,
		v_partitioned,
		v_ityp_owner,
		v_ityp_name,
		v_ityp_parameters
	    from 	
		undo_all_indexes
	    where 	
		owner = user
		and index_name = upper(v_index);



	    -- Get the columns in the index
	    for rec in c_ind_columns(v_index,v_table) loop
		if v_columns is not null then
		    v_columns := v_columns || ',';
		end if;
		v_columns := v_columns || '"' || rec.column_name || '"';
	    end loop;		


	    -- Is this a domain index? If so, the create statement is special.
	    if (v_index_type = 'DOMAIN') then
	       v_create_stmt := 
		'CREATE INDEX "' || user || '"."' || upper(v_index) || 
		'" ON ' || v_cluster || ' "' || user || '"."' ||
		v_table || '" ( ' || v_columns || ' ) INDEXTYPE IS "'
		|| v_ityp_owner || '"."' || v_ityp_name || '" PARAMETERS('''
		|| v_ityp_parameters || ''')';

	    elsif (v_index_type not in ('BITMAP','UNIQUE','NORMAL')) then
		    raise not_supported;

	    else

	      -- Construct the recreate statement and insert into 
	      -- the storage table for later recreation.
	      v_create_stmt := 
		'CREATE ' || v_index_type ||
		' INDEX "' || user || '"."' || upper(v_index) || 
		'" ON ' || v_cluster || ' "' || user || '"."' ||
		v_table || '" ( ' || v_columns || ' ) ';


	      -- If this is a partitioned index, assemble the per-partition
	      -- details
	      if (v_partitioned = 'YES') then
		v_create_stmt := v_create_stmt || ' LOCAL (';

		for part_rec in c_ind_partitions(v_index) loop
		    if (part_rec.partition_position != 1) then
			v_create_stmt := v_create_stmt || ', ';
		    end if;

		    v_create_stmt := v_create_stmt  
			|| 'PARTITION '   || part_rec.partition_name
			|| ' INITRANS '   || part_rec.ini_trans 
			|| ' MAXTRANS '   || part_rec.max_trans 
			|| ' TABLESPACE ' || part_rec.tablespace_name 
			|| ' STORAGE (' ||
				'INITIAL ' || part_rec.initial_extent ||
				' NEXT ' || part_rec.next_extent ||
				' PCTINCREASE ' || part_rec.pct_increase
			|| ' ) PCTFREE '  || part_rec.pct_free;

		end loop;

		v_create_stmt := v_create_stmt || ' )';

	      else
		v_create_stmt := v_create_stmt ||
		' INITRANS ' || v_ini_trans || 
		' MAXTRANS ' || v_max_trans ||
--		' MINEXTENTS ' || v_min_extents || 
--		' MAXEXTENTS ' || v_max_extents || 
		' TABLESPACE ' || v_tablespace || ' STORAGE (' ||
		' INITIAL ' || v_initial_ext || ' NEXT ' || v_next_ext ||
		' PCTINCREASE ' || v_pct_increase || ' ) PCTFREE ' || 
		v_pct_free;
	      end if;
	    end if;


	    -- Delete any old recreate statments
	    delete from undo_storage
	    where
		user_name = user
		and object_type = 'INDEX'
		and object_name = upper(v_index);

	    -- Insert a new uncreate statement
	    insert into undo_storage 
		(user_name,undo_date,object_type,object_subtype,
			object_name, object_parent, redo_statement)
		values 
		(user,sysdate,'INDEX',v_index_type,
			upper(v_index), v_table, v_create_stmt);


	    -- Drop the index
	    v_cursor_id := dbms_sql.open_cursor;
            dbms_sql.parse(v_cursor_id,
		       'drop index "' || user || '".' ||
		       '"' || upper(v_index) || '"',
		       dbms_sql.native);
            dbms_sql.close_cursor(v_cursor_id);		

	    commit;

	EXCEPTION
	    when others then 
		rollback to savepoint undo_savept;
		raise;
	END;	


	----------------------------------------------------------------
	-- DISABLE procedure for indexes                              --
	----------------------------------------------------------------
	PROCEDURE disable_index (
		v_index in undo_all_indexes.index_name%type
				) is

		v_cursor_id	integer := null;
		v_create_stmt	long := null;
		v_index_type	undo_all_indexes.index_type%type;
		v_table		undo_all_indexes.table_name%type;
	  	v_partitioned 	undo_all_indexes.partitioned%type;

		cursor c_partitions is
		select partition_name from undo_all_ind_partitions
		where index_owner = user and index_name = upper(v_index);

	BEGIN

	    -- What kind of index is this?
	    select index_type, table_name, partitioned 
	    into v_index_type, v_table, v_partitioned
	    from undo_all_indexes
	    where owner = user
	      and index_name = upper(v_index);

	    savepoint undo_savept;

	    -- Delete any old recreate statements
	    delete from undo_storage
	    where user_name = user
	    and object_name = v_index
	    and object_type = 'INDEX';

	    v_create_stmt := 
		'ALTER INDEX "' || user || '"."' || upper(v_index) || 
		'" REBUILD'; 

	    -- If it's partitioned, we'll need to rebuild each one.
	    if (v_partitioned = 'YES') then

		for rec in c_partitions loop

		    insert into undo_storage 
			(user_name,undo_date,object_type,object_subtype,
			object_name, object_parent, redo_statement)
		    values 
			(user,sysdate,'INDEX',v_index_type,
			upper(v_index), v_table, 
			v_create_stmt || ' PARTITION ' || rec.partition_name);
		end loop;

	    else
		insert into undo_storage
		    (user_name,undo_date,object_type,object_subtype,
		     object_name, object_parent, redo_statement)
		values
		    (user,sysdate,'INDEX',v_index_type,
		     upper(v_index), v_table, v_create_stmt);
	    end if;

	    -- Disable the index
	    v_cursor_id := dbms_sql.open_cursor;
            dbms_sql.parse(v_cursor_id,
		       'alter index "' || user || '".' ||
		       '"' || upper(v_index) || '" unusable',
		       dbms_sql.native);
            dbms_sql.close_cursor(v_cursor_id);		

	    commit;

	EXCEPTION
	    when others then
		rollback to savepoint undo_savept;
		raise;
	END;	


	----------------------------------------------------------------
	-- Drop all bitmap indexes on a table.                        --
	----------------------------------------------------------------
	PROCEDURE unindex(
		v_table_name in all_objects.object_name%type,
		v_index_type in undo_all_indexes.index_type%type
			  ) is

		cursor c_indexes is 
		    select index_name from undo_all_indexes
		    where owner = user
		      and table_name = upper(v_table_name)
		      and index_type = upper(v_index_type);	 
	BEGIN

	        for rec in c_indexes loop
		    uncreate_index(rec.index_name);
		end loop;
	END;  


	----------------------------------------------------------------
	-- Generic RECREATE procedure. Works for any object type.     --
	-- This version accepts no extra parameters.		      --
	----------------------------------------------------------------
	PROCEDURE recreate(
		v_object_type in all_objects.object_type%type,
		v_object_name in all_objects.object_name%type
			  ) is
	BEGIN
		recreate(v_object_type,v_object_name,'');
	END;


	----------------------------------------------------------------
	-- Generic RECREATE procedure. Works for any object type.     --
	----------------------------------------------------------------
	PROCEDURE recreate(
		v_object_type in all_objects.object_type%type,
		v_object_name in all_objects.object_name%type,
		v_option_clause in varchar2
				) is

		v_cursor_id integer;

		cursor c_statements is
		select redo_statement,rowid from undo_storage
		where user_name = user
		and object_type = upper(v_object_type)
		and object_name = upper(v_object_name);

		-- TEMP
		v_redo_stmt varchar2(4000);
	
	BEGIN

		-- Loop through each recreate statement
		for rec in c_statements loop

		    -- TEMP
		    select replace(rec.redo_statement,'replace','')
		    into v_redo_stmt from dual;


		    -- Execute it.
		    v_cursor_id := dbms_sql.open_cursor;
                    dbms_sql.parse(v_cursor_id,
--				   rec.redo_statement || v_option_clause,
				   v_redo_stmt || v_option_clause,
			       	   dbms_sql.native);
                    dbms_sql.close_cursor(v_cursor_id);
			dbms_output.put_line('Y');

		    -- Delete the recreate statement from the storage table.
		    delete from undo_storage
		    where rowid = rec.rowid;

		end loop;

		commit;

	EXCEPTION
	    when others then
		raise;
	END;		


	----------------------------------------------------------------
	-- ENABLE procedures. Just call recreate		      --
	----------------------------------------------------------------
	PROCEDURE enable(
		v_object_type in all_objects.object_type%type,
		v_object_name in all_objects.object_name%type
			  ) is
	BEGIN
		recreate(v_object_type,v_object_name,'');
	END;

	PROCEDURE enable(
		v_object_type in all_objects.object_type%type,
		v_object_name in all_objects.object_name%type,
		v_option_clause in varchar2
			  ) is
	BEGIN
		recreate(v_object_type,v_object_name,v_option_clause);
	END;


	----------------------------------------------------------------
	-- Recreate bitmap indexes on a table			      --
	----------------------------------------------------------------
	PROCEDURE reindex(
		v_table_name in all_objects.object_name%type,
		v_index_type in undo_all_indexes.index_type%type
			  ) is
	BEGIN
		reindex(v_table_name,v_index_type,'');
	END;  


	----------------------------------------------------------------
	-- Recreate bitmap indexes on a table with an option clause   --
	----------------------------------------------------------------
	PROCEDURE reindex(
		v_table_name in all_objects.object_name%type,
		v_index_type in undo_all_indexes.index_type%type,
		v_option_clause in varchar2
			  ) is

		cursor c_indexes is 
		    select object_name from undo_storage
		    where user_name = user
		      and object_parent = upper(v_table_name)
		      and object_type = 'INDEX'
		      and object_subtype = upper(v_index_type);

	BEGIN
		for rec in c_indexes loop
		    recreate('INDEX',rec.object_name,v_option_clause);
		end loop;
	END;  


		
END undo;
/
show errors










