Job Scheduling in ORACLE

Lets start with creating a stored procedure that we will use to create a job.

CREATE PROCEDURE hr.test_proc1  AS
BEGIN
 insert into test1 values(25,'baju');
END;

Use this following script to create a Scheduled Job.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'job_test1',
   JOB_TYPE           =>  'STORED_PROCEDURE',
   JOB_ACTION         =>  'hr.test_proc1',
   START_DATE         =>  '10-JUN-12 12.23.00 PM',
   repeat_interval    =>  'FREQ=MINUTELY;INTERVAL=2');
END;

Now, you can enable, disable or even drop this job using following Commands.

--to enable 
DBMS_SCHEDULER.ENABLE ('job_test1');

--to disable
DBMS_SCHEDULER.DISABLE ('job_test1');

--to drop
EXEC DBMS_SCHEDULER.DROP_JOB('job_test1');

--to retrieve details of job
select * from user_SCHEDULER_JOBS;

Moreover, you can change Schedule Attributes and make a test run using following Commands.

-- to change Time Interval
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('job_test1','repeat_interval', 'FREQ=Minutely;INTERVAL=2');

--to make a test run
EXEC DBMS_SCHEDULER.RUN_JOB('job_test1');

Thanks
A Rahim Khan

2012

06/12

CATEGORY

Oracle DBA

TAGS

DBMS_SCHEDULER
Oracle Disable Job
Oracle Jobs
Oracle Schedule

Write comment