| General Information
|
| Note:
This package is deprecated and has been supplanted by DBMS_SCHEDULER.
|
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsjob.sql |
| First Available |
7.3.4 |
| Dependencies |
| job$
|
|
|
| dba_jobs |
all_jobs |
user_jobs |
|
dba_jobs_running |
all_jobs_running |
user_jobs_running |
|
| Exceptions |
| Error Code |
Reason |
| ORA-00001 |
Unique constraint (SYS.I_JOB_JOB) violated |
| ORA-23420 |
Interval must evaluate to a time in the future |
|
| Job Intervals |
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 |
| Security Model |
Execute is granted to PUBLIC with GRANT
option |
| |
| BACKGROUND_PROCESS |
Indicates whether execution is a background
process or foreground process.
For internal use only. |
dbms_job.background_process RETURN
BOOLEAN |
set serveroutput on
DECLARE
b BOOLEAN;
BEGIN
IF dbms_job.background_process THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/ |
| |
| BROKEN |
How a job becomes 'broken' |
Oracle has failed to successfully execute
the job after 16 attempts.
or
You have marked the job as broken, using the procedure DBMS_JOB.BROKEN
Once a job has been marked as broken, Oracle will not attempt to execute the job until it
is either marked not broken, or forced to be execute by calling the DBMS_JOB.RUN. |
Force a job to broken status |
dbms_job.broken (
job IN BINARY_INTEGER,
broken IN BOOLEAN,
next_date IN DATE DEFAULT SYSDATE); |
| exec dbms_job.broken(42, TRUE> |
| The following example marks job 14144 as not broken and
sets its next execution date to - - the following Monday: |
exec dbms_job.broken(14144,
FALSE, NEXT_DAY(SYSDATE, 'MONDAY')); |
| Force a broken job to run and fix it |
exec dbms_job.broken(JOB=>&job_no,
NEXT_DATE=>SYSDATE + &when_plus_sysdate, broken=>FALSE); |
| |
| CHANGE |
Change A Job's Attributes |
dbms_job.change(
job IN BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE,
interval IN VARCHAR2,
instance IN BINARY_INTEGER DEFAULT NULL,
force IN BOOLEAN
DEFAULT FALSE); |
| exec dbms_job.change(14144,
NULL, NULL, 'SYSDATE + 3'); |
| |
| INSTANCE |
Assign a specific instance to execute a job |
dbms_job.instance(
job IN BINARY_INTEGER,
instance IN BINARY_INTEGER,
force IN BOOLEAN DEFAULT FALSE); |
SELECT instance_number
FROM gv$instance;
exec dbms_job.instance(42, 1); |
| |
| INTERVAL |
| Reset the job interval |
dbms_job.interval (
job IN BINARY_INTEGER,
interval IN VARCHAR2); |
| exec dbms_job.interval(179,
'TRUNC(SYSDATE)
+ 24/24'); |
| Note: Use TRUNC(SYSDATE)
to keep the job interval from drifting. |
| |
| ISUBMIT |
Submit a job with a user specified job
number |
dbms_job.isubmit (
job IN BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE,
interval IN VARCHAR2 DEFAULT 'NULL',
no_parse IN BOOLEAN DEFAULT FALSE);
Note: no_parse indicates whether to parse job PL/SQL at time of submission (FALSE) or execution (TRUE) |
| exec dbms_job.isubmit(4242, 'MYPROC',
SYSDATE); |
| |
| IS_JOBQ |
Undocumented
Thank you Laszlo Vincze for the correction |
dbms_job.is_jobq RETURN BOOLEAN; |
set serveroutput on
DECLARE
b BOOLEAN;
BEGIN
IF dbms_job.is_jobq THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/ |
| |
| NEXT_DATE |
| Reset next execution date and time for a
job |
dbms_job.next_date(
job IN BINARY_INTEGER,
next_date IN DATE); |
| exec dbms_job.next_date(134, SYSDATE
+ 1/24); |
| |
| REMOVE |
| To remove a job in the job queue |
dbms_job.remove (job IN BINARY_INTEGER); |
SELECT job
FROM user_job;
exec dbms_job.remove(23); |
| |
| RUN |
| Force a job to run immediately and, if it
was broken, reset it to not broken |
dbms_job.run(
job IN BINARY_INTEGER,
force IN BOOLEAN DEFAULT FALSE); |
| exec dbms_job.run(job_no); |
| |
| SUBMIT |
Submit Parameters |
JOB An output
parameter, this is the identifier assigned to the job you created. You must use this job
number 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.
|
dbms_job.submit(
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'),
'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'),
'TRUNC(SYSDATE+(10/24/60),''MI'')');
-- 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'),
'TRUNC(SYSDATE+(2/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'),
'TRUNC(SYSDATE+(30/24/60),''MI'')'); |
| |
| USER_EXPORT |
Produces the text of a call to re-create
the given job |
dbms_job.user_export (
job IN BINARY_INTEGER,
mycall IN OUT VARCHAR2); |
SELECT job
FROM user_jobs;
set serveroutput on
DECLARE
callstr VARCHAR2(500);
BEGIN
dbms_job.user_export(186, callstr);
dbms_output.put_line(callstr);
END;
/ |
Alters instance affinity (8i and after) and
preserves compatibility |
dbms_job.user_export (
job IN BINARY_INTEGER,
mycall IN OUT VARCHAR2,
myinst IN OUT VARCHAR2); |
set serveroutput on
DECLARE
callstr VARCHAR2(500);
inststr VARCHAR2(50);
BEGIN
dbms_job.user_export(186, callstr);
dbms_output.put_line(callstr);
dbms_output.put_line(inststr);
END;
/ |
| |
| WHAT |
Change a job's definition |
dbms_job.what (
job IN BINARY_INTEGER,
what IN VARCHAR2); |
| exec dbms_job.what(42, 'YOURPROC'); |
| |
| DBMS_JOB Demo |
Jobs Demonstration |
CREATE TABLE job_table (
now DATE);
CREATE OR REPLACE VIEW job_view AS
SELECT TO_CHAR(now, 'DD-MON-YYYY HH:MI:SS') NOW
FROM job_table;
exec job_call
SELECT * FROM job_view;
SELECT job, next_date, next_sec
FROM dba_jobs;
|
| |
| DBMS_JOB
Related Queries |
| Jobs Running |
SELECT r.sid, r.job, r.this_date,
r.this_sec, SUBSTR(what,1,40) what
FROM dba_jobs_running r,dba_jobs j
WHERE r.job = j.job; |
| User Jobs |
col job format 99999
SELECT job, next_date, next_sec, failures, broken, SUBSTR(what,1,40) DESCRIPTION
FROM user_jobs; |
| Forcing mandatory log switches |
I just used a dbms_job that calls a
proc that switches
the logfile every n minutes (e.g. 30) if it hasn't been switched since then. |
