Skip to content

Instantly share code, notes, and snippets.

@mehdip2007
Created April 5, 2020 10:31
Show Gist options
  • Save mehdip2007/4ee7664b5906a35e0d1f4eda284298e4 to your computer and use it in GitHub Desktop.
Save mehdip2007/4ee7664b5906a35e0d1f4eda284298e4 to your computer and use it in GitHub Desktop.
The snippet is related to creating, dropping, enabling a job in oracle database
// Drop Part
BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB
(job_name => 'ABL_DBOBJECTS.RECON_REVOKING_DUNNING');
END;
/
// Creation Part
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'ABL_DBOBJECTS.RECON_REVOKING_DUNNING'
,start_date => TO_TIMESTAMP_TZ('2019/12/06 08:15:55.425802 Asia/Dubai','yyyy/mm/dd hh24:mi:ss.ff tzr')
,repeat_interval => 'FREQ=DAILY;BYHOUR=8,10,12,15,18,22;BYMINUTE=0;BYSECOND=0'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => 'ABL_DBOBJECTS.PRC_RECON_REVOKING_DUNNING'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'ABL_DBOBJECTS.RECON_REVOKING_DUNNING'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'ABL_DBOBJECTS.RECON_REVOKING_DUNNING'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'ABL_DBOBJECTS.RECON_REVOKING_DUNNING'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'ABL_DBOBJECTS.RECON_REVOKING_DUNNING'
,attribute => 'MAX_RUNS');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'ABL_DBOBJECTS.RECON_REVOKING_DUNNING'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'ABL_DBOBJECTS.RECON_REVOKING_DUNNING'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'ABL_DBOBJECTS.RECON_REVOKING_DUNNING'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'ABL_DBOBJECTS.RECON_REVOKING_DUNNING'
,attribute => 'AUTO_DROP'
,value => FALSE);
// Enabling part
SYS.DBMS_SCHEDULER.ENABLE
(name => 'ABL_DBOBJECTS.RECON_REVOKING_DUNNING');
END;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment