title: Is there a good workaround to the Oracle IMPDP REMAP_SCHEMA issue with triggers (ORA-39083, ORA-00942)?
body: <p>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: </p>
  
  <pre><code>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 ...
  </code></pre>
  
  <p>The reason for this is because the create SQL still refers to OLD_SCHEMA. It does say in the Oracle documentation that:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>IMHO this is a bit of a cop out by Oracle but that's another discussion!</p>
  
  <p>According to Oracle Metalink note 750783.1, the workaround is to:</p>
  
  <blockquote>
    <ol>
    <li>Create a SQLFILE to include the relevant DDL command(s):</li>
    </ol>
  </blockquote>
  
  <pre><code>      impdp system/****** directory=test_dp
        DUMPFILE=export_schemas.dmp
       remap_schema=u1:u2 sqlfile=script.sql
  </code></pre>
  
  <blockquote>
    <ol start="2">
    <li>Extract the affected DDL from the written SQLFILE and correct the
    schema reference. Then execute the command manually.</li>
    </ol>
  </blockquote>
  
  <p>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.</p>
  
  <p>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?</p>