| 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 |
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_JOB'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_JOB'; |
| 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 |
| Subprograms |
|
| |
| 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;
/ |
| |
| 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.removejob 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 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. |
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;
CREATE OR REPLACE PROCEDURE do_job IS
BEGIN
INSERT INTO job_table
(now)
VALUES
(SYSDATE);
COMMIT;
END do_job;
/ |
CREATE OR REPLACE PROCEDURE job_call AS
JobNo user_jobs.job%TYPE;
BEGIN
dbms_job.submit(JobNo, 'begin do_job; end;', SYSDATE,
'SYSDATE + 36/86400');
COMMIT;
END;
/ |
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. Something users of the library might want to consider writing for theselves. |
