The
DBMS_JOB package allows a user to schedule a job to run at a specified time. A job is submitted to a job queue and runs at the specified time. The user can also input a parameter that specifies how often the job should run. A job can consist of any PL/SQL code.
Source: {ORACLE_HOME}/rdbms/admin/dbmsjob.sqSNP Background Processes
SNP processes run in the background and implement database snapshots and job queues. If an SNP process fails, Oracle restarts it without affecting the rest of the database. An SNP process can run one job at a time with a maximum of ten SNP processes running simultaneously. The INIT.ORA initialization file contains three parameters that control the behavior of the SNP processes:
Parameters |
Description |
JOB_QUEUE_PROCESSES |
How many processes to start. If set to zero, no jobs are executed. Default is 0. Range is 0..1000. |
JOB_QUEUE_INTERVAL (obsolete in 9i) |
How long an interval the process will sleep before checking for a new job. Default is 60 sec. Range is 1..3600 sec |
JOB_QUEUE_KEEP_CONNECTIONS |
Controls whether an SNP process closes any remote database connections. Default is False. Range is True/False. |
Job Owner
The person who submits a job to the job queue is considered the job's owner. The job's owner is the only one who can alter the job, force the job to run, or remove the job from the queue. If you need to look up any information regarding a submitted job, DBA_JOBS and USER_JOBS contain a lot of information such as the job number, user, status, etc.
Dependencies
job$
· dba_jobs
· all_jobs
· user_jobs
· dba_jobs_running
· all_jobs_running
· user_jobs_running
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
Changing Scheduled Jobs
The
DBMS_JOB.CHANGE procedure alters a specified job that has already been submitted to the job queue. The job description, the time at which the job will be run, the interval between executions of the job, and the instance and force of a job can all be altered.
Syntax: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);
job- Number of the job being run. What - PL/SQL procedure to run. next_date - Date of the next refresh. interval - Date function evaluated immediately before the job starts running. instance - When a job is submitted, specifies which instance can run the job. This defaults to NULL, which indicates that instance affinity is not changed. force - If this is FALSE, then the specified instance must be running. Otherwise, the routine raises an exception. If this is TRUE, then any positive integer is acceptable as the job instance.SQL Code:EXECUTE DBMS_JOB.CHANGE(1, null, sysdate+3, null);
Change the WHAT
The definition of a job can be altered by calling the
DBMS_JOB.WHAT procedure.
Syntax: DBMS_JOB.CHANGE (
job IN BINARY_INTEGER,
what IN VARCHAR2);
SQL Code: execute DBMS_JOB.WHAT(3,Begin SEND_MESSAGE(); End;');
Changing the Next Date
The
DBMS_JOB.NEXT_DATE procedure alters the next execution time for a specified job.
Syntax:DBMS_JOB.NEXT_DATE (JOB IN BINARY_INTEGER,
NEXT_DATE IN DATE);
SQL Code:execute DBMS_JOB.NEXT_DATE(4,SYSDATE+3);
Changing the Interval
The DBMS_JOB.INTERVAL procedure alters the interval between executions for a specified job.
Syntax:
DBMS_JOB.INTERVAL (job IN BINARY_INTEGER,
interval IN VARCHAR2);
Assign job to RAC instance
Assign a specific RAC instance to execute a job:
Syntax:
dbms_job.instance(
job IN BINARY_INTEGER,
instance IN BINARY_INTEGER,
force IN BOOLEAN DEFAULT FALSE);
SELECT instance_number FROM gv$instance;
SQL Code:
EXECUTE DBMS_JOB.INSTANCE(job=>123, instance=>1);
SQL Code:
SELECT JOB,SUBSTR(WHAT,1,35),NEXT_DATE, NEXT_SEC,INTERVAL
FROM DBA_JOBS;
ISUBMIT
Submit a job with a user specified job number
Syntax:
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);
N.B: 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
dbms_job.is_jobq RETURN BOOLEAN;
SQL Code:
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;
/
BROKEN Jobs
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
N.B: 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.
Syntax:
dbms_job.broken (
job IN BINARY_INTEGER,
broken IN BOOLEAN,
next_date IN DATE DEFAULT SYSDATE);
Run a job now
To force immediate job execution:
EXEC dbms_job.run(job_no);
Mark a job as not broken
If a job fails to successfully execute it will go into a broken state after 16 attempts. To reset the job use following command
EXEC dbms_job.broken(jobno, FALSE);
Monitor User jobs
See created jobs: SQL Code:
SELECT job, next_date,
next_sec,
failures,
broken,
SUBSTR(what,1,40) DESCRIPTION
FROM user_jobs;
Jobs that are currently running:
SQL Code:
select
job j,
to_char ( last_date, 'hh24:mi:ss' ) last,
to_char ( this_date, 'hh24:mi:ss' ) this,
to_char ( next_date, 'hh24:mi:ss' ) next,
broken b,
failures f,
interval,
what
from user_jobs;
Remove a submitted job
Syntax:
dbms_job.remove(job IN BINARY_INTEGER);
EXECUTE DBMS_JOB.REMOVE(jobno);
Example of DBMS_JOBS
--Job For Email
declare
jobno integer;
begin
dbms_job.submit(
job => jobno,
what => 'begin PROC_SEND_EMAIL_AT_11_45(); end;',
-- provide resolution to the second
-- midnight hour of minute of units per day
-- of current day day to run hr to run ( 1 second )
next_date => trunc(sysdate) + ((23 * (60*60) + ( 60*45)) * ( 1/(60*60*24))),
interval => 'trunc(sysdate + 1) + ((23 * (60*60) + ( 60*50)) * ( 1/(60*60*24)))'
);
commit;
end;
/
What Sessions are Running the Jobs
set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;
Viewing scheduled dbms_jobs
set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;
What Jobs are Actually Running
set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;
