IMPDP Reports ORA-942 and ORA-39083 During Importing Schema Objects
HTML clipboard .style1 { color: #0000FF; } .style2 { color: #FF0000; }
| 文档 ID: | 750783.1 | 类型: | PROBLEM | |
| Modified Date: | 22-JAN-2009 | 状态: | PUBLISHED | |
In this Document
Symptoms
Cause
Solution
Applies to:
Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 11.1.0.7
This problem can occur on any platform.
Symptoms
During DataPump import of schema objects (such as triggers, functions, etc.) the errors ORA-39083 and ORA-942 occur when the object code contains hard coded schema references and the schema doesn’t exist on the target database.
The parameter REMAP_SCHEMA will not prevent this issue, like demonstrated in next example:
-- create test user
create user u1 identified by u1 default tablespace users temporary tablespace temp;
grant connect, resource to u1;
-- create test objects
connect u1/u1
create table tab1
(
col1 number,
col2 number
);
create or replace trigger trg1
before insert or update on u1.tab1 <== schema reference!
for each row
begin
:new.col2 := :new.col1*2;
end;
/
insert into tab1 values (1,1);
commit;
-- export
host expdp system/passwd directory=test_dp dumpfile=export_schemas.dmp schemas=u1
-- import (using REMAP_SCHEMA)
drop user u1 cascade;
drop user u2 cascade;
host impdp system/passwd directory=test_dp dumpfile=export_schemas.dmp remap_schema=u1:u2
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** directory=test_dp DUMPFILE=export_schemas.dmp remap_schema=u1:u2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “U2″.”TAB1″ 0 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39083: Object type TRIGGER failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE TRIGGER “U2″.”TRG1″
before insert or update on u1.tab1 <== old schema reference
for each row
begin
:new.col2 := :new.col1*2;
end;
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ completed with 1 error(s) at 16:55:55
Cause
The parameter REMAP_SCHEMA cannot prevent this issue as remapping will be done only on source schemas.
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 triggers, types, views, procedures, and packages.
Solution
Once ORA-39083 and ORA-00942 are reported during import of objects such as triggers, types, views, procedures, and packages you need to correct the schema references embedded in the body of those objects.
- 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.
-- new object type path is: SCHEMA_EXPORT/TABLE/TRIGGER
-- CONNECT U2
CREATE TRIGGER "U2"."TRG1"
before insert or update on u2.tab1 <== correct schema name here
for each row
begin
:new.col2 := :new.col1*2;
end;
/