Category Archives: Data Pump

Export/Import Scheduler Jobs

The Oracle Utilities documentation (data pump, sql*ldr, etc.) is a bit light with regards to the object types and the corresponding names used in the INCLUDE parameter as it only refers to DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS and TABLE_EXPORT_OBJECTS views which apparently do not have a very useful description in the COMMENTS column for all object paths.

On the web there are several resources showing how to import Oracle Scheduler jobs from a schema dump by creating the sql file (using SQLFILE parameter) and then extracting the relevant DBMS_SCHEDULER.CREATE_JOB commands from the DDL sql file. This seems a bit cumbersome to automate. Fortunately there is a much simpler way to do this using the right object type in the INCLUDE parameter: PROCOBJ

Assuming schema_dump.dmp contains a complete schema dump and you just want to import the Scheduler jobs run following impdp command:

$ impdp <username>/<password> dumpfile=schema_dump.dmp include=PROCOBJ

Foot note: All tests have been run on a 10g 10.2.0.4 database.