Create and scheduler a DBMS scheduler job

 For create a job in DBMS Scheduler, exact method is first create a schedule, then create a program and then a job.

  1. Create a schedule
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
Schedule_name => 'DAILYSALESJOB_SCHED',
Start_date => SYSTIMESTAMP,
Repeat_interval =>'FREQ=DAILY;BYHOUR=11; BYMINUTE=30',
Comments => 'DAILY SALES JOB'
);
END;
/
  1. Create a program

In this we are calling a stored procedure as an example, you can call anything thing pl/sql block, stored procedure, function etc.

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'DAILYSALESJOB',
program_type => 'STORED_PROCEDURE',
program_action => 'SCOTT.SALES_PROC'
number_of_arguments =>0,
enabled => TRUE,
comments => 'DAILY SALES JOB'
);
END;
/
  1. Create the job in DBMS Scheduler.
Begin
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DAILYSALESJOB_START',
program_name => 'DAILYSALESJOB',
schedule_name => 'DAILYSALESJOB_SCHED',
enabled => FLASE,
comments => 'daily sales job'
); END;
/
  1. ENABLE THE JOB.
EXEC DBMS_SCHEDULER.ENABLE('DAILYSALESJOB_START');

Drop the DBMS Scheduler job

DROP the Scheduler with DBMS Scheduler.

BEGIN
DBMS_SCHEDULER.DROP_SCHEDULE(
schedule_name => 'DAILYSALESJOB_SCHED',
force => TRUE
); END;
/

Drop the program.

 BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM ( program_name => 'DAILYSALESJOB');
End;
/

Drop the job in dbms scheduler.

Exec DBMS_SCHEDULER.DROP_JOB (job_name => 'DAILYSALESJOB_START');

Related posts

How to deal with ORA-00020: maximum number of processes (%s) exceeded

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database 

Công cụ tự động khai báo datafile – Oracle