The easy way to get the ddl of an dbms scheduler job is :
select dbms_metadata.get_ddl(‘PROCOBJ’,’JOB_NAME’,’JOB_OWNER’) from dual;
set pagesize 299 set long 999 select dbms_metadata.get_ddl('PROCOBJ','DBACLASS_PURGE','DBAUSER') from dual; DBMS_METADATA.GET_DDL('PROCOBJ','DBACLASS_PURGE','DBAUSER') -------------------------------------------------------------------------------- BEGIN dbms_scheduler.create_job('"DBACLASS_PURGE"', job_type=>'STORED_PROCEDURE', job_action=> 'DBAUSER.DBA_CREATE_DBACIRCUIT_PATH' , number_of_arguments=>0, start_date=>TO_TIMESTAMP_TZ('06-JUN-2017 06.58.16.001208000 AM ASIA/BAHRAIN','DD -MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=> 'FREQ=HOURLY;INTERVAL=2;' , end_date=>NULL, job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>FALSE,comments=> 'DBACLASS_PURGE' ); dbms_scheduler.enable('"DBACLASS_PURGE"'); COMMIT; END;
But what if the job owner is SYS? Will the same syntax work?
SQL> select dbms_metadata.get_ddl('PROCOBJ','CLEANUP_ONLINE_IND_BUILD','SYS') from dual; ERROR: ORA-31603: object "CLEANUP_ONLINE_IND_BUILD" of type PROCOBJ not found in schema "SYS" ORA-06512: at "SYS.DBMS_METADATA", line 6069 ORA-06512: at "SYS.DBMS_METADATA", line 8666 ORA-06512: at line 1 no rows selected
Throwing error!!!!
Well we have an workaround for this. Lets copy the scheduler job to another schema.
SQL> select owner,job_name from dba_scheduler_jobs where job_name='CLEANUP_ONLINE_IND_BUILD'; OWNER JOB_NAME ------------ ---------------------------------- SYS CLEANUP_ONLINE_IND_BUILD SQL> exec dbms_scheduler.copy_job('SYS.CLEANUP_ONLINE_IND_BUILD','DBACLASS.CLEANUP_ONLINE_IND_BUILD'); PL/SQL procedure successfully completed. SQL> select owner,job_name from dba_scheduler_jobs where job_name='CLEANUP_ONLINE_IND_BUILD'; OWNER JOB_NAME ------------ ---------------------------------- SYS CLEANUP_ONLINE_IND_BUILD DBACLASS CLEANUP_ONLINE_IND_BUILD -- >> another copy of that job
Now we have the sImilar job under the user DBACLASS, so we can get the DDL without any error.
SQL> select dbms_metadata.get_ddl('PROCOBJ','CLEANUP_ONLINE_IND_BUILD','BSSDBA') from dual; DBMS_METADATA.GET_DDL('PROCOBJ','CLEANUP_ONLINE_IND_BUILD','BSSDBA') -------------------------------------------------------------------------------- BEGIN dbms_scheduler.create_job('"CLEANUP_ONLINE_IND_BUILD"', job_type=>'PLSQL_BLOCK', job_action=> 'declare myinterval number; begin myinterval := dbms_pdb.cleanup_task(2); if myinterval <> 0 then next_date := systimestamp + numtodsinterval(myinterval, ''second''); end if; end;' , number_of_arguments=>0, start_date=>TO_TIMESTAMP_TZ('07-JUL-2014 05.53.58.299360000 AM -07:00','DD-MON-R RRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=> 'FREQ = HOURLY; INTERVAL = 1' , end_date=>NULL, job_class=>'"SCHED$_LOG_ON_ERRORS_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comme nts=> 'Cleanup Online Index Build' ); COMMIT; END;