Schema Level EXPDP Does Not Export Jobs Created With DBMS_JOB Package (Doc ID 875904.1)

Last updated on JULY 24, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.4 [Release 10.2]
Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.1.0.6 [Release 11.1]
Information in this document applies to any platform.
***Checked for relevance on 24-Jul-2013***

Symptoms

Jobs created in a schema with the DBMS_JOB package are not exported by a schema level EXPDP of this same schema. No errors are reported.

For example:

connect / as sysdba
create user foo identified by foo;
grant connect, resource, create job to foo;
conn foo/foo
create table footab (str1 varchar2(10));
declare
job number(11);
begin
sys.dbms_job.submit(job => job,
what => 'insert into footab values(''Foo'');',
next_date => to_date(SYSDATE, 'dd-mm-yyyy hh24:mi:ss'), interval => 'sysdate+1');
commit;
end;
/


Export the foo schema shows no errors:

expdp foo/foo schemas=foo dumpfile=foo.dmp logfile=foo.log directory=dp_dir

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 17 August, 2009 11:35:22

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "FOO"."SYS_EXPORT_SCHEMA_01": foo/******** schemas=foo dumpfile=foo.dmp logfile=foo.log
directory=dp_dir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/JOB
. . exported "FOO"."FOOTAB" 4.914 KB 1 rows
Master table "FOO"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded


Check if the job was exported by doing an impdp with SQLFILE parameter against the created dump file:

impdp foo/foo dumpfile=foo.dmp directory=dp_dir sqlfile=DDL.txt nologfile=y


The contents of the generated DDL.txt SQL file will only show:

-- CONNECT FOO
-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'),
export_db_name=>'<db name>', inst_scn=>'530396');
COMMIT;
END;
/

-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "FOO"."FOOTAB"
( "STR1" VARCHAR2(10)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;


The statement to create the job is missing as it is not exported.

Note:
  • The issue is specific to schema level export.
  • A full Data Pump export will export the job(s).
  • Jobs created with the new dbms_scheduler package are exported.

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