IMPDP Reports ORA-942 and ORA-39083 During Importing Schema Objects (Doc ID 750783.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 20-Jul-2015***

Symptoms

During DataPump import of schema objects (such as triggers, functions, etc.), errors ORA-39083 and ORA-942 occur when the object code contains hard coded schema references and the schema does not exist in the target database.

The parameter REMAP_SCHEMA will not prevent this issue, like demonstrated in next example:

TEST CASE

-- 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


IMPDP LOGFILE

Import: Release 10.2.0.4.0 - Production on Tuesday, 10 November, 2008 16:55:51
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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms