About Oracle 10g schedule job, oracleschedule
-- Job permission
Grant create job to somebody;
-- Job creation
Begin
Dbms_scheduler.create_job (
Job_name => 'agent _ LIQUIDATION_JOB ',
Job_type => 'stored _ PROCEDURE ',
Job_action => 'agent _ LIQUIDATION. LIQUIDATION ', -- stored procedure name
Start_date => sysdate,
Repeat_interval => 'freq = MONTHLY; INTERVAL = 1; BYMONTHDAY = 1; BYHOUR = 1; BYMINUTE = 0; BYSECOND = 0', -- MONTHLY, INTERVAL: 1 (month ), 1st of every month, AM
Comments => 'execute the agent Division Process'
);
End;
/
-- Job execution time test
DECLARE
Start_date date;
Return_date_after date;
Next_run_date date;
BEGIN
Start_date: = sysdate; -- to_timestamp_tz ('10-OCT-2004 10:00:00 ', 'dd-MM-YYYY HH24: MI: ss ');
Return_date_after: = start_date;
FOR I IN1..10LOOP
Values ('freq = MONTHLY; INTERVAL = 1; BYMONTHDAY = 1; BYHOUR = 1; BYMINUTE = 0; BYSECOND = 0', start_date, return_date_after, next_run_date );
DBMS_OUTPUT.PUT_LINE ('Next _ run_date: '| to_char (next_run_date, 'yyyy-mm-dd HH24: MI: ss '));
Return_date_after: = next_run_date;
End loop;
END;
/
-- Job query
Select owner, job_name, state from dba_scheduler_jobs;
Select job_name, state from user_scheduler_jobs;
-- Job enabled
Begin
Dbms_scheduler.enable ('backup _ job ');
End;
/
-- Job run
Begin
Dbms_scheduler.run_job ('cola _ job', TRUE); -- true indicates synchronous execution
End;
/
-- Stop job (not easy to use)
Begin
Dbms_scheduler.stop_job (job_name => 'cola _ job', force => TRUE );
End;
/
-- Delete a job (useful for stopping a job)
Begin
Dbms_scheduler.drop_job (job_name => 'cola _ job', force => TRUE );)
End;
/
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.