This is one handy script which helps how to extract DDL for a dbms scheduler job
SQL> set long 999999
SQL> set pagesize 222
SQL> set long 1000
Syntax:- select dbms_metadata.get_ddl(‘PROCOBJ’,’JOB_NAME’,’OWNER OF THE JOB’) from dual;
SQL>select dbms_metadata.get_ddl(‘PROCOBJ’,’PROD_DB_BACKUP’,’SYSTEM’) from dual;
DBMS_METADATA.GET_DDL(‘PROCOBJ’,’PROD_DB_BACKUP’,’SYSTEM’)
——————————————————————————–
BEGIN
dbms_scheduler.create_job(‘”PROD_DB_BACKUP”‘,
job_type=>’PLSQL_BLOCK’, job_action=>
‘BEGIN
jde_schema_backup(”(””PRODDTA””,””PRODCTL””,””SY920””,””SVM920′
”’,””PD920””)”
,”soumyad@wizertech.in”
);
END;’
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ(’10-JAN-2019 09.48.32.526098000 AM ASIA/KOLKATA’,’
DD-MON-RRRR HH.MI.SSXFF AM TZR’,’NLS_DATE_LANGUAGE=english’), repeat_interval=>
‘FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI,SAT;BYHOUR=0;BYMINUTE=10’
, end_date=>NULL,
job_class=>’”DEFAULT_JOB_CLASS”‘, enabled=>FALSE, auto_drop=>TRUE,comments=>
NULL
);
COMMIT;
END;
Leave a Reply