problem with impdp and jobs

IMPDP doesn't remap Database Jobs to new User Schema [ID 1122424.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.2.0 - 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:

• Submit a job using DBMS_JOB.SUBMIT
• Export the schema using Data Pump Export (expdp)
• Import the schema into a different user schema by using REMAP

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

Issue is caused by Bug:9535916.

Solution

FIX

Fixed in RDBMS release 12.1

WORKAROUND

None.

PATCH

At the time creating this article there was no patch available.
Please check "My Oracle Support" for availability of Patch:9535916.
If necessary contact Oracle Support

IMPORTANT
---------
This patch requires that some post-install actions are completed.
Please follow the README documentation.


Patch Post Install Instructions:
--------------------------------
After the patch has been applied/rollbacked please reload the package/s into
the database. To do this connect as SYS and execute the following;

SQL> @?/rdbms/admin/catnomta
SQL> @?/rdbms/admin/dbmsmeta.sql
SQL> @?/rdbms/admin/dbmsmeti.sql
SQL> @?/rdbms/admin/dbmsmetu.sql
SQL> @?/rdbms/admin/dbmsmetb.sql
SQL> @?/rdbms/admin/dbmsmetd.sql
SQL> @?/rdbms/admin/dbmsmet2.sql
SQL> @?/rdbms/admin/catmeta.sql
SQL> @?/rdbms/admin/prvtmeta.plb
SQL> @?/rdbms/admin/prvtmeti.plb
SQL> @?/rdbms/admin/prvtmetu.plb
SQL> @?/rdbms/admin/prvtmetb.plb
SQL> @?/rdbms/admin/prvtmetd.plb
SQL> @?/rdbms/admin/prvtmet2.plb
SQL> @?/rdbms/admin/catmet2.sql