CREATE OR REPLACE PACKAGE tvd_fba_helper IS -- --------------------------------------------------------------------------- -- Trivadis AG, Basel/Bern/Lausanne/Zürich/Düsseldorf -- Frankfurt/Freiburg i.Br./Hamburg/München/Stuttgart/Wien -- Switzerland/Germany/Austria Internet: http://www.trivadis.com -- --------------------------------------------------------------------------- -- -- File-Name........: tvd_fba_helper.sql -- Developer........: Philipp Salvisberg (philipp.salvisberg@trivadis.com) -- Date.............: January 2013 -- Description......: Helper Package for Flashback (Data) Archive -- also known as Total Recall -- Provides procedures to update SYS.TAB$ to -- enable and disable DML on table -- - SYS_FBA_DDL_COLMAP_ and -- - SYS_FBA_TCRV_ -- IMPORTANT: Use at your own risk. -- Group/Privileges.: Needs to be installed under SYS or a DBA user with -- direct update privileges on SYS.TAB$ -- Requirements.....: 11gR2 (tested on 11.2.0.3.4) -- 12cR1 (tested on 12.1.0.1.0) -- -- --------------------------------------------------------------------------- -- Changes: -- 01.01.2013 PhS Initial version -- 25.03.2014 PhS Added procedure fix_col_map_validity -- --------------------------------------------------------------------------- /** * Enables DML on SYS_FBA_DDL_COLMAP_. * Please use DBMS_FLASHBACK_ARCHIVE.DISSASSOCIATE_FBA and ALTER TABLE * instead of this procedure whenever possible. * @param in_owner_name owner of the FBA table * @param in_table_name FBA table name */ PROCEDURE disassociate_col_map(in_owner_name IN VARCHAR2, in_table_name IN VARCHAR2); /** * Disables DML on SYS_FBA_DDL_COLMAP_. * @param in_owner_name owner of the FBA table * @param in_table_name FBA table name */ PROCEDURE reassociate_col_map(in_owner_name IN VARCHAR2, in_table_name IN VARCHAR2); /** * Enables DML on SYS_FBA_TCRV_. * @param in_owner_name owner of the FBA table * @param in_table_name FBA table name */ PROCEDURE disassociate_tcrv(in_owner_name IN VARCHAR2, in_table_name IN VARCHAR2); /** * Disables DML on SYS_FBA_TCRV_. * @param in_owner_name owner of the FBA table * @param in_table_name FBA table name */ PROCEDURE reassociate_tcrv(in_owner_name IN VARCHAR2, in_table_name IN VARCHAR2); /** * Fix the validity of SYS_FBA_DDL_COLMAP_ * according the SYS_FBA_HIST_ validity * @param in_owner_name owner of the FBA table * @param in_table_name FBA table name */ PROCEDURE fix_col_map_validity(in_owner_name IN VARCHAR2, in_table_name IN VARCHAR2); END tvd_fba_helper; / CREATE OR REPLACE PACKAGE BODY tvd_fba_helper IS -- -- private ccnstants -- c_unprotected_fba_table CONSTANT INTEGER := 536870912; c_protected_fba_table CONSTANT INTEGER := 9126805504; c_sys_fba_ddl_colmap CONSTANT VARCHAR2(20) := 'SYS_FBA_DDL_COLMAP_'; c_sys_fba_tcrv CONSTANT VARCHAR2(20) := 'SYS_FBA_TCRV_'; c_sys_fba_hist CONSTANT VARCHAR2(20) := 'SYS_FBA_HIST_'; -- -- patch_tab$ -- PROCEDURE patch_tab$(in_owner_name IN VARCHAR2, in_table_name IN VARCHAR2, in_fba_name_prefix IN VARCHAR2, in_property IN INTEGER) IS l_object_id NUMBER; BEGIN -- get object_id of SYS_FBA_... table to be patched SELECT object_id INTO l_object_id FROM all_objects WHERE object_type = 'TABLE' AND owner = in_owner_name AND object_name = in_fba_name_prefix || (SELECT object_id FROM all_objects WHERE owner = in_owner_name AND object_name = in_table_name AND object_type = 'TABLE'); -- patch Oracle Data Dictionary -- IMPORTANT NOTICE: -- never update SYS_FBA_HIST_ table (may leave FBA in inconsistent state!) -- use dbms_flashback_archive API instead UPDATE sys.tab$ SET property = in_property WHERE obj# = l_object_id; COMMIT; -- make dictionary change visible EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH SHARED_POOL'; END patch_tab$; -- -- disassociate_col_map -- PROCEDURE disassociate_col_map(in_owner_name IN VARCHAR2, in_table_name IN VARCHAR2) IS BEGIN patch_tab$(in_owner_name => in_owner_name, in_table_name => in_table_name, in_fba_name_prefix => c_sys_fba_ddl_colmap, in_property => c_unprotected_fba_table); END disassociate_col_map; -- -- reassociate_col_map -- PROCEDURE reassociate_col_map(in_owner_name IN VARCHAR2, in_table_name IN VARCHAR2) IS BEGIN patch_tab$(in_owner_name => in_owner_name, in_table_name => in_table_name, in_fba_name_prefix => c_sys_fba_ddl_colmap, in_property => c_protected_fba_table); END reassociate_col_map; -- -- disassociate_tcrv -- PROCEDURE disassociate_tcrv(in_owner_name IN VARCHAR2, in_table_name IN VARCHAR2) IS BEGIN patch_tab$(in_owner_name => in_owner_name, in_table_name => in_table_name, in_fba_name_prefix => c_sys_fba_tcrv, in_property => c_unprotected_fba_table); END disassociate_tcrv; -- -- reassociate_tcrv -- PROCEDURE reassociate_tcrv(in_owner_name IN VARCHAR2, in_table_name IN VARCHAR2) IS BEGIN patch_tab$(in_owner_name => in_owner_name, in_table_name => in_table_name, in_fba_name_prefix => c_sys_fba_tcrv, in_property => c_protected_fba_table); END reassociate_tcrv; -- -- reassociate_tcrv -- TODO: add proper exception handling -- PROCEDURE fix_col_map_validity(in_owner_name IN VARCHAR2, in_table_name IN VARCHAR2) IS l_archive_table_name dba_flashback_archive_tables.flashback_archive_name%TYPE; l_col_map_table_name dba_flashback_archive_tables.flashback_archive_name%TYPE; l_min_startscn NUMBER; l_col_map_state NUMBER; BEGIN -- get the archive table SELECT archive_table_name INTO l_archive_table_name FROM dba_flashback_archive_tables WHERE owner_name = in_owner_name AND table_name = in_table_name; -- check state of ddl_col_map, must not contain terminated columns l_col_map_table_name := c_sys_fba_ddl_colmap || REPLACE(l_archive_table_name, c_sys_fba_hist); EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || in_owner_name || '.' || l_col_map_table_name || ' WHERE endscn IS NOT NULL' INTO l_col_map_state; IF l_col_map_state = 0 THEN -- get the SCN for new validity EXECUTE IMMEDIATE 'SELECT MIN(startscn) FROM ' || in_owner_name || '.' || l_archive_table_name INTO l_min_startscn; IF l_min_startscn IS NOT NULL THEN disassociate_col_map(in_owner_name => in_owner_name, in_table_name => in_table_name); EXECUTE IMMEDIATE 'UPDATE ' || in_owner_name || '.' || l_col_map_table_name || ' SET startscn = ' || l_min_startscn || ' WHERE startscn > ' || l_min_startscn; COMMIT; reassociate_col_map(in_owner_name => in_owner_name, in_table_name => in_table_name); END IF; END IF; END fix_col_map_validity; END tvd_fba_helper; /