This is probably very simple but i already exported all but the scheduled jobs i created to subsequent schemas.

How can I create a sql script of the scheduled jobs I created in Oracle? I know in SQL developer, you can use the Database Export wizard to export DDLs and data but you can't use it to export prior scheduled jobs.

I am trying not to have to recreate all my jobs for each environment as I go. Can you please help!


Assuming that we are talking about dbms_scheduler jobs (rather than, say, jobs scheduled using the dbms_job package), you can use the dbms_metadata package to generate the DDL. Specifically, the dbms_metadata.get_ddl function. Something like

SELECT dbms_metadata.get_ddl( 'PROCOBJ', <<job name>>, <<schema>> )
  FROM dual

should work, for example.

Here is a list of the object types that dbms_metadata.get_ddl supports.

Here is an example of using the PROCOBJ trick to get dbms_metadata to script out a job.

| improve this answer | |
  • 2
    even now, dbms_metadata does not support dbms_scheduler jobs. The JOBs mentioned in the list are the dbms_job type of jobs. – ik_zelf Aug 13 '14 at 14:42
  • @ik_zelf - D'oh, you're right. Updated my answer to use the old PROCOBJ trick. I thought that was no longer necessary but apparently I was mistaken. – Justin Cave Aug 13 '14 at 15:09
  • hey, nice trick, I did not know that. – ik_zelf Aug 13 '14 at 15:51
  • Thanks!! You just save me hours trying to replicate job creation manually ;) – user3821501 Aug 14 '14 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.