2

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!

Improve this question

1 Answer 1

Reset to default
3

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
4
  • 2
    even now, dbms_metadata does not support dbms_scheduler jobs. The JOBs mentioned in the list are the dbms_job type of jobs.
    – user953
    Aug 13, 2014 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, 2014 at 15:09
  • hey, nice trick, I did not know that.
    – user953
    Aug 13, 2014 at 15:51
  • Thanks!! You just save me hours trying to replicate job creation manually ;)
    – user3821501
    Aug 14, 2014 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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