oracle impdp compilation,IMPDP ORA-39083: Object type TRIGGER failed

dupicate schema but warnings about trigger

(2010-05-26 15:10:00)

标签：

when import schema to the other schema, but there are some

warnings;

ORA-39082: Object type TRIGGER:"IDM_SERVICES_DEV2"."

TS_ENTITLEMENT_GLOBAL_SEQ" created with compilation warnings

ORA-39082: Object type

TRIGGER:"IDM_SERVICES_DEV2"."TS_ENTITLEMENT_GLOBAL_SEQ" created

with compilation warnings

ORA-39082: Object type

TRIGGER:"IDM_SERVICES_DEV2"."TS_RULE_GLOBAL_SEQ" created with

compilation warnings

ORA-39082: Object type

TRIGGER:"IDM_SERVICES_DEV2"."TS_RULE_GLOBAL_SEQ" created with

compilation warnings

why cause it? Because of the table owner is the before

schema.

how to deal with it?

impdp system/oracle directory=tmp_dir dumpfile=services.expdp.dmp

logfile=services.impdp.log REMAP_SCHEMA=SERVICES:SERVICES_DEV2

sqlfile=script.sql

then import it again

impdp system/oracle directory=tmp_dir dumpfile=services.expdp.dmp

logfile=services.impdp.log

REMAP_SCHEMA=SERVICES:SERVICES_DEV2

and change the triggers on the script.sql

#########################################

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?