DBMS_METADATA is great for getting any object DDL, but with scheduler jobs it's not so intuitive.
Rather than JOB, or whatever, it takes PROCOBJ:
fakeuser@dev01>BEGIN
2 DBMS_SCHEDULER.create_job (
3 job_name => 'my_fake_job',
4 job_type => 'PLSQL_BLOCK',
5 job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
6 start_date => SYSTIMESTAMP,
7 repeat_interval => 'freq=hourly; byminute=0',
8 end_date => NULL,
9 enabled => TRUE,
10 comments => 'My fake job.');
11 end;
12 /
PL/SQL procedure successfully completed.
fakeuser@dev01>select dbms_metadata.get_ddl('PROCOBJ',JOB_NAME) FROM USER_SCHEDULER_JOBS;
DBMS_METADATA.GET_DDL('PROCOBJ',JOB_NAME)
--------------------------------------------------------------------------------
BEGIN
dbms_scheduler.create_job('"MY_FAKE_JOB"',
job_type=>'PLSQL_BLOCK', job_action=>
'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('06-DEC-2010 01.26.37.186707000 PM +00:00','DD-MON-R
RRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'freq=hourly; byminute=0'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comments=>
'My fake job.'
);
dbms_scheduler.enable('"MY_FAKE_JOB"');
COMMIT;
END;
Very Nice!
ReplyDeleteTkz!