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

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: