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:
- 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
- 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?
1 comment:
After ON clause, schema qualifier is not required. data pump import creates trigger in new schema without schema qualifier.
Post a Comment