Is there a good workaround to the Oracle IMPDP REMAP_SCHEMA issue with triggers (ORA-39083, ORA-00942)?

Question

One can use the Oracle data pump import tool (IMPDP.EXE) to import one schema into another using the REMAP_SCHEMA option. However there is an issue in that triggers are not properly remapped. This leads to the trigger not being created at all with an error as follows:

ORA-39083: Object type TRIGGER failed to create with error: ORA-00942: table or view does not exist Failing sql is: CREATE TRIGGER "**NEW_SCHEMA**"."METER_ALARMS_BI"   BEFORE INSERT ON
**OLD_SCHEMA**.METER_ALARMS ...

The reason for this is because the create SQL still refers to OLD_SCHEMA. It does say in the Oracle documentation that:

The mapping may not be 100 percent complete, because there are certain schema references that Import is not capable of finding. For example, Import will not find schema references embedded within the body of definitions of types, views, procedures, and packages.

IMHO this is a bit of a cop out by Oracle but that's another discussion!

According to Oracle Metalink note 750783.1, the workaround is to:

1. Create a SQLFILE to include the relevant DDL command(s):

      impdp system/****** directory=test_dp
      DUMPFILE=export_schemas.dmp
     remap_schema=u1:u2 sqlfile=script.sql

2. Extract the affected DDL from the written SQLFILE and correct the schema reference. Then execute the command manually.

This is not a good way to do it especially if you have many failed objects and want to automate the process of combining multiple schema for in field upgrading of databases.

Has anyone found a better way to do this? I need a solution that must be 100% reliable if its to be used in the field. I could parse the generated SQL file but can one get this 100% correct? Is there not some way to intercept the CREATE SQL statements execute by IMPDP and correct it on the fly while importing? Could one patch the DMP file directly?

Answer 1

I think it depends on whether the schema names can appear in your code as part of something that is not a schema name. For example, do you have variable names that include the same characters as the schema name. If not then I don't think it would be that hard to script up a process that edits the generated trigger create scripts replacing the old schema with the new one. Maybe you can use datapump to export/import the object types that don't have text code (not triggers, packages, procedures, functions, etc.) and then dump out the SQL for the code objects and just replace old schema with new one.

If the old schema name appears in places that you don't want to replace it would be harder to do. You might extract the code objects and try to create them and collect all of the errors. Then get the names of the objects that it failed on and try replacing the oldschema.objectname with newschema.objectname based on the errors and rerun.

Example of how you might edit the schema in the trigger text assuming there were no strings like oldschema. that you don't want to replace:

Example

SQL> 
SQL> set define off
SQL> 
SQL> drop table test1.tab1;

Table dropped.

SQL> drop table test1.tab2;

Table dropped.

SQL> 
SQL> create table test1.tab1
  2  (
  3     col1 number,
  4     col2 number
  5  );

Table created.

SQL> 
SQL> create table test1.tab2
  2  (
  3     col1 number,
  4     col2 number
  5  );

Table created.

SQL> 
SQL> create or replace trigger test1.trg1
  2  before insert or update on test1.tab1
  3  for each row
  4  begin
  5    :new.col2 := :new.col1*2;
  6  end;
  7  /

Trigger created.

SQL> 
SQL> create or replace trigger test1.trg2
  2  before insert or update on test1.tab2
  3  for each row
  4  begin
  5    :new.col2 := :new.col1*2;
  6  end;
  7  /

Trigger created.

SQL> 
SQL> drop table clobout;

Table dropped.

SQL> 
SQL> create table clobout (doc clob);

Table created.

SQL> 
SQL> declare
  2  h NUMBER; --handle returned by OPEN
  3  th NUMBER; -- handle returned by ADD_TRANSFORM
  4  doc CLOB;
  5  BEGIN
  6  
  7  -- Specify the object type.
  8  h := DBMS_METADATA.OPEN('TRIGGER');
  9  
 10  -- Use filters to specify the particular object desired.
 11  DBMS_METADATA.SET_FILTER(h,'SCHEMA','TEST1');
 12  
 13  -- Request that the schema name be modified.
 14  th := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY');
 15  DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_SCHEMA','TEST1','TEST2');
 16  
 17  -- Request that the metadata be transformed into creation DDL.
 18  th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
 19  
 20  dbms_metadata.set_transform_param(th,'SQLTERMINATOR',true);
 21  
 22  -- Fetch the triggers.
 23  
 24  LOOP
 25    doc := DBMS_METADATA.FETCH_CLOB(h);
 26    EXIT WHEN (doc is null);
 27    insert into clobout values (doc);
 28    commit;
 29  END LOOP;
 30  
 31  -- Release resources.
 32  DBMS_METADATA.CLOSE(h);
 33  END;
 34  /

PL/SQL procedure successfully completed.

SQL> 
SQL> -- update schema name in triggers
SQL> 
SQL> update clobout set doc=replace(doc,'test1.','test2.');

2 rows updated.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> select doc from clobout;

  CREATE OR REPLACE EDITIONABLE TRIGGER "TEST2"."TRG1"
before insert or update on test2.tab1
for each row
begin
  :new.col2 := :new.col1*2;
end;
/
ALTER TRIGGER "TEST2"."TRG1" ENABLE;


  CREATE OR REPLACE EDITIONABLE TRIGGER "TEST2"."TRG2"
before insert or update on test2.tab2
for each row
begin
  :new.col2 := :new.col1*2;
end;
/
ALTER TRIGGER "TEST2"."TRG2" ENABLE;


SQL> 
SQL> spool off