IMPDP doesn't remap Database Jobs to new User Schema (Doc ID 1122424.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.4 [Release 10.1 to 11.2]
Information in this document applies to any platform.

Symptoms

When you export/import a user schema which contains a database job, the Data Pump Import (Impdp) doesn't remap the job to a new schema when REMAP parameter is used. The job is still owned by the original user while other objects such as the PLSQL code of the database job are owned by the new user.
Sequence of events leading to the problem:
 



The following test case illustrates this in more detail:


######################################
## Before exporting
######################################

-- Create some test users
drop user u1 cascade;
drop user u2 cascade;

create user u1 identified by u1 default tablespace users;
grant dba to u1;

-- Create a sample job
CREATE OR REPLACE PROCEDURE u1.simple
is
BEGIN
  null;
END Simple;
/

connect u1/u1
variable jobno number;
execute DBMS_JOB.SUBMIT(:jobno,'u1.simple;',sysdate,'sysdate+1/1444')


-- Check job status
col LOG_USER form a10
col PRIV_USER form a10
col SCHEMA_USER form a10
col what form a20

select job,log_user,priv_user,schema_user,what
from dba_jobs where lower(what) like '%simple%';

       JOB LOG_USER   PRIV_USER  SCHEMA_USE WHAT
---------- ---------- ---------- ---------- --------------------
        83 U1         U1         U1         u1.simple;



-- Check owner of procedure
col OWNER form a20
col OBJECT_NAME form a20
col OBJECT_TYPE form a20

select owner,object_name,object_type
from dba_objects where object_name='SIMPLE';

OWNER                OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------- --------------------
U1                   SIMPLE               PROCEDURE


-- Create schema export
expdp system/****** DIRECTORY=test_dp DUMPFILE=export_schemas.dmp schemas=u1




######################################
## After import with REMAP parameter
######################################


-- Drop user schema
drop user u1 cascade;

-- Now import and remap to new user schema U2
impdp system/****** directory=test_dp DUMPFILE=export_schemas.dmp
remap_schema=u1:u2


-- Check user
select username from dba_users where username in ('U1','U2');

USERNAME
------------------------------
U2



-- Check job
col LOG_USER form a10
col PRIV_USER form a10
col SCHEMA_USER form a10
col what form a20

select job,log_user,priv_user,schema_user,what
from dba_jobs where lower(what) like '%simple%';

       JOB   LOG_USER  PRIV_USER SCHEMA_USE WHAT
---------- ---------- ---------- ---------- --------------------
        83         U1         U1         U1 u1.simple;


-- Check object
col OWNER form a20
col OBJECT_NAME form a20
col OBJECT_TYPE form a20

select owner,object_name,object_type
from dba_objects where object_name='SIMPLE';

OWNER                OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------- --------------------
U2                   SIMPLE               PROCEDURE




SUMMARY
-------
Schema data (user U1) was correctly remapped to new user U2, however the job is still owned by non-existing user U1.


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