For how many years have you been working
with physical servers that are starving your database of the memory
necessary to deploy important new performance features such as the Result
Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and
Full Database Caching? Too long? Contact me to learn how to improve all
queries ... not just some queries.
This package is deprecated and has been supplanted by DBMS_SCHEDULER. If you have any code using this package it is recommended that you move it to DBMS_SCHEDULER as part of migrating to 12c
Unique constraint (SYS.I_JOB_JOB) violated
Interval must evaluate to a time in the future
Execute daily 'SYSDATE + 1'
Execute once per week 'SYSDATE + 7'
Execute hourly 'SYSDATE + 1/24'
Execute every 10 min. 'SYSDATE + 10/1440'
Execute every 30 sec. 'SYSDATE + 30/86400'
Do not re-execute NULL
JOB An output parameter, this is the identifier assigned to the job you created. You must use this jobnumber whenever you want to alter or remove the job.
WHAT This is the PL/SQL code you want to have executed. The WHAT parameter must end with a semi-colon.
NEXT_DATE The next date when the job will be run. The default value is SYSDATE.
INTERVAL The date function that calculates the next time to execute the job. The default value is NULL. INTERVAL must evaluate to a future point in time or NULL.
This parameter is a VARCHAR2 and must be enclosed in single quotes.
NO_PARSE This is a flag. If NO_PARSE is set to FALSE (the default), Oracle parses the procedure associated with the job. If NO_PARSE is set to TRUE, Oracle parses the procedure associated with the job the first time that the job is executed.
If, for example, you want to submit a job before you have created the tables associated with the job, set NO_PARSE to TRUE.
Submit a job with a job number selected from sys.jobseq
Many thanks for Kieron Hardy for contributing these examples.
JOB OUT BINARY_INTEGER,
WHAT IN VARCHAR2,
NEXT_DATE IN DATE DEFAULT SYSDATE,
INTERVAL IN VARCHAR2 DEFAULT 'NULL',
NO_PARSE IN BOOLEAN DEFAULT FALSE,
INSTANCE IN BINARY_INTEGER DEFAULT 0,
FORCE IN BOOLEAN DEFAULT FALSE);
-- To run everynight at midnight starting tonight exec dbms_job.submit(:v_JobNo, 'proc1;', TRUNC(SYSDATE)+1, 'TRUNC(SYSDATE)+1');
-- To run every hour, on the hour, starting at the top of the hour
exec dbms_job.submit(:v_JobNo, 'proc2;', TRUNC(SYSDATE+(1/24), 'HH'),
-- To run every hour, starting now
exec dbms_job.submit(:v_JobNo, 'proc3;', INTERVAL => 'SYSDATE+(1/24)');
-- To run every ten minutes at 0,10,20,etc. minutes past the hour, starting at the top of the hour
exec dbms_job.submit(:v_JobNo, 'proc4;', TRUNC(SYSDATE+(1/24), 'HH'),
-- To run every 2 min., on the minute, starting at the top of the minute
exec dbms_job.submit(:v_JobNo, 'proc5;', TRUNC(SYSDATE+(1/24/60), 'MI'),
-- To run every two minutes, starting now
exec dbms_job.submit(:v_JobNo, 'proc6;', INTERVAL => 'SYSDATE+(2/24/60)');
-- To run every half hour, starting at the top of the hour
exec dbms_job.submit(:v_JobNo, 'proc7;', TRUNC(SYSDATE+(1/24), 'HH'),