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_<object_id> and
   --                       - SYS_FBA_TCRV_<object_id> 
   --                     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)
   --
   -- ---------------------------------------------------------------------------
   -- Changes:
   -- 01.01.2013 PhS Initial version
   -- ---------------------------------------------------------------------------

   /**
   * Enables DML on SYS_FBA_DDL_COLMAP_<object_id>.
   * 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_<object_id>.
   * @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_<object_id>.
   * @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_<object_id>.
   * @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);
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_';

   --
   -- 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;
END tvd_fba_helper;
/