Introduction
Sometimes it is necessary to move quarantine configurations between databases. Maybe after running a functional or performance test. This page explains how to do this.
Step-by-Step
1. Using SQL*Plus, connect to the source database as a user with the correct administrative privileges, and create a staging table using the DBMS_SQLQ.CREATE_STGTAB_QUARANTINE procedure.
BEGIN DBMS_SQLQ.CREATE_STGTAB_QUARANTINE ( staging_table_name => 'TBL_STG_QUARANTINE'); END; /
2. Add the quarantine configurations into the staging table, which you want to transfer to the destination database. The following example adds all the quarantine configurations starting with the name QUARANTINE_CONFIG_ into the staging table TBL_STG_QUARANTINE. Note that the DBMS_SQLQ.PACK_STGTAB_QUARANTINE function returns the number of quarantine configurations added to the staging table.
DECLARE quarantine_configs NUMBER; BEGIN quarantine_configs := DBMS_SQLQ.PACK_STGTAB_QUARANTINE( staging_table_name => 'TBL_STG_QUARANTINE', name => 'QUARANTINE_CONFIG_%'); END; /
3. Export the staging table TBL_STG_QUARANTINE to a dump file using the Oracle Data Pump Export utility.
4. Transfer the dump file from the source database system to the destination database system.
5. On the destination database system, import the staging table TBL_STG_QUARANTINE from the dump file into the destination database using the Oracle Data Pump Import utility.
6. Using SQL*Plus, connect to the destination database as a user with the administrative privileges, and create the quarantine configurations from the imported staging table.
DECLARE quarantine_configs NUMBER; BEGIN quarantine_configs := DBMS_SQLQ.UNPACK_STGTAB_QUARANTINE( staging_table_name => 'TBL_STG_QUARANTINE'); END; /
Published 1st April 2022
