I am trying to extract the DDL for our scheduler jobs but keep coming up against a brick wall. I've tried doing an expdp/impdp using the SQLFILE option but can't find the jobs here, tried a natty bit of code to pull the info out of the DB using DBMS_METADATA which worked for ordinary DBA_JOBS but not for scheduler based jobs.
Any ideas how I can get hold of this info?
This is perhaps not the prettiest way either, but what I have done is to click on the "Create Like" button for the job in EM. Enter a new job name and then click on the "Show SQL" button. That gives you all of the code. I then cut and paste into text file and change all references to the new job name back to existing.
The only other way I can think of is to use an internal routine which does this.
First select the object_id of the job by selecting from obj$ (where name='JOB_NAME') then use a procedure like
create or replace procedure export_job(oid number) as
tmpbuf varchar2(4000) := 'NULL';
while tmpbuf is not null loop
tmpbuf := dbms_sched_job_export.create_exp(oid, '', dummy);
where 12345 is the obj# selected from obj$
The only disadvantage of this is that job argument values will not be included in the output.
Hope this helps,
Thanks for your input ... I actually ended up managin to get the SQLFILE to contain user owned scheduler jobs, but SYS must be obfuscated in some way as I couldnt find them anywhere. I then had to use the 'Create Like' from OEM to get some other info for additional jobs.
Sheesh, you'd think it would be a bit easier than that!
Reason for that is pretty simple. User-level objects in SYS (like tables, stored procedures, jobs etc) are simply not exported since SYS is intended only for system objects.
I agree that there should be a simpler way to do this though. It's probably a bug that dbms_metadata doesn't work on Scheduler jobs.
Last post on this is from 2007. I'm wondering if there have been any improvements since then, eg. in 11g. I'm still looking for documentation about dbms_sched_job_export.create_exp because I must be doing something wrong with it. I'm trying to call it for a known scheduler job in my system with an object ID of 315289, using this code (based on the code given earlier in this thread):
You can get the DDL for a job like this:
select dbms_metadata.get_ddl('PROCOBJ', 'JOB_NAME', 'OWNER_NAME') from dual;
You can use this go gather the DDL for a number of the DBMS_SCHEDULER objects, including PROGRAMS and CHAINs [will include the CHAIN and the STEPS, butnot the RULES - these you must get from a *_scheduler_chain_rules VIEW]