Here is how to program a scheduled job in Oracle DB to run at specific date and time:
The following job will run at 17:00 every day:
SET DEFINE OFF;
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'execute immediate ''alter session set time_zone = ''Europe/Athens'''';BEGIN xxtest_pkg.calculate;END;'
,next_date => TRUNC(SYSDATE) + 17/24
,interval => 'TRUNC(SYSDATE+1) + 17/24'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/
If the job should run every 5 minutes then we modify the following:
,next_date => SYSDATE + 5/1440
,interval => ‘SYSDATE+ 5/1440’
,interval => ‘SYSDATE+ 5/1440’
(1440 = 24 * 60)
Some useful queries:
select
job,
log_user subu,
what proc,
total_time total_time,
to_char(last_date,'MM/DD') LAST_DATE,
substr(last_sec,1,5) LAST_TIME, to_char(next_date,'MM/DD') NEXT_DATE,
substr(next_sec,1,5) NTEXT_TIME,
failures fail,
decode(broken,'Y','N','Y') ok
from
sys.dba_jobs;
begin
dbms_job.stop_job(226);
end;
begin
DBMS_JOB.BROKEN(186,FALSE);
end;
BEGIN
DBMS_JOB.run(288);
END;