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