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

This entry was posted in Administration. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s