DBA_JOB exmples

-- Run MYPROC procedure daily at 1:00 AM
variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'MYPROC;',trunc(sysdate)+1,'trunc(sysdate)+1+1/24', TRUE, :instno);
COMMIT;
END;
/
print jobno

-- Run MYPROC monthly on the first of every month at 2:00 AM
variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno,'MYPROC;',trunc(sysdate)+1,'trunc(add_months(sysdate,1),'''MM''')+2/24', TRUE, :instno);
COMMIT;
END;
/
print jobno

-- Run MYPROC Every 15 Minutes Starting at the Next 15 Minute Interval
variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'MYPROC;', trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/15)+1)*15)/(24*60), 'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/15)+1)*15)/(24*60)', TRUE, :instno);
COMMIT;
END;
/
print jobno

-- Run MYPROC Every 30 Minutes Starting at the Next 30 Minute Interval
variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'MYPROC;', trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60), 'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/30)+1)*30)/(24*60)', TRUE, :instno);
COMMIT;
END;
/
print jobno

-- Run MYPROC Every 1 Hour
variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'MYPROC;', TRUNC(sysdate+1/24,'HH'), 'TRUNC(SYSDATE+1/24,''HH'')', TRUE, :instno);
COMMIT;
END;
/
print jobno

BEGIN
-- Job defined entirely by the CREATE JOB procedure.
DBMS_SCHEDULER.create_job (
job_name => 'myproc_name',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN myproc(''CREATE_PROGRAM (BLOCK)''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;
/
BEGIN
-- Job defined by an existing program and schedule.
DBMS_SCHEDULER.create_job (
job_name => 'myproc_name',
program_name => 'test_plsql_block_prog',
schedule_name => 'test_hourly_schedule',
enabled => TRUE,
comments => 'Job defined by an existing program and schedule.');
END;
/
BEGIN
-- Job defined by an existing program and inline schedule.
DBMS_SCHEDULER.create_job (
job_name => 'test_prog_job_definition',
program_name => 'test_plsql_block_prog',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined by existing program and inline schedule.');
END;
/
BEGIN
-- Job defined by existing schedule and inline program.
DBMS_SCHEDULER.create_job (
job_name => 'test_sched_job_definition',
schedule_name => 'test_hourly_schedule',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN my_job_proc(''CREATE_PROGRAM (BLOCK)''); END;',
enabled => TRUE,
comments => 'Job defined by existing schedule and inline program.');
END;