This is a replacement for DBMS_JOBS
What jobs are there
-
SELECT * FROM ALL_SCHEDULER_JOBS ;
Viewing job details
-
select *
-
FROM ALL_SCHEDULER_JOB_LOG
-
order by log_date desc;
-
-
select *
-
from all_SCHEDULER_JOB_RUN_DETAILS
-
order by log_date desc;
Add a job
-
set serveroutput on;
-
declare
-
isPresent number := 0;
-
l_scanExcptionJobName varchar2(255) := 'Name_Of_Job';
-
l_scanExcptionCode varchar2(255) := 'XXIC_JMS_UTILS.MoveExceptionsToErrorQueue;';
-
l_ExceptionScanInterval varchar2(255) := 'FREQ=MINUTELY';
-
-
begin
-
SELECT count(*) into isPresent FROM ALL_SCHEDULER_JOBS WHERE job_name = l_scanExcptionJobName;
-
if (isPresent <>1 ) then
-
dbms_output.put_line('Createing schedular job');
-
-
Dbms_scheduler.create_job (
-
job_name => l_scanExcptionJobName,
-
job_type => 'PLSQL_BLOCK',
-
job_action => l_scanExcptionCode,
-
start_date => sysdate,
-
repeat_interval => l_ExceptionScanInterval,
-
enabled => TRUE,
-
comments => 'This process deos something'
-
);
-
end;
Freq | Code |
---|---|
Hourly | FREQ=HOURLY; |
Every Minute | FREQ=MINUTELY; |
Every 5 minutes | freq=MINUTELY;interval=5; |
Every Day at 22:30:00 | freq=daily;byhour=22;byminute=30;bysecond=0; |
Every Week | FREQ=WEEKLY; |
Every Month | FREQ=MONTHLY; |
http://psoug.org/reference/dbms_scheduler.html
Remove a job
-
set serveroutput on;
-
declare
-
isPresent number := 0;
-
l_scanExcptionJobName varchar2(255) := 'Name_Of_Job';
-
begin
-
SELECT count(*) into isPresent FROM ALL_SCHEDULER_JOBS WHERE job_name = l_scanExcptionJobName;
-
if (isPresent<>0) then
-
dbms_output.put_line('Deleting Exception -> Error Job');
-
DBMS_SCHEDULER.DROP_JOB(l_scanExcptionJobName);
-
end if;
-
end;
RJM Article Type
Quick Reference