DBMS JOB Package is used as Oracle Scheduler

DBMS_JOB package is used in Oracle database for scheduling your jobs. It allows a user to schedule a job to run at a specified time. Oracle introduce new scheduler(DBMS SCHEDULER) to overcome its limitation. You can see the syntax for creation, deletion, scheduled, execute with help of DBMS_JOB Package.

SNP is the Background Processes which implements database snapshots and job queues. An SNP process can run one job at a time with a maximum of ten SNP processes running simultaneously.
Three parameters that control the behavior of the SNP processes:
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.

Syntax for create the Job with dbms_job Package:

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);


Create DBMS JOB with Example

We can create the job with following example but we need to specify one return variable which will return us jobno. We used the job number for execution, deletion of job.

VARIABLE jobno number;
Begin
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+2/24,'HH'), 'trunc(SYSDATE+2/24,''HH'')', TRUE, :instno);
commit;
end;
/
Print jobno


Check the DBMS Jobs present in the database

SELECT job, next_date, next_sec, failures, broken, SUBSTR(what,1,40) DESCRIPTION FROM dba_jobs;


Check DBMS Jobs running status

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;


For execute the DBMS job manually:

dbms_job.run( job IN BINARY_INTEGER, force IN BOOLEAN DEFAULT FALSE);
example:
exec dbms_job.run(job_no);


Use following Time format for schedule activity on specific intervals:

Execute daily -- 'SYSDATE + 1'
Execute once per week -- 'SYSDATE + 7'
Execute hourly -- 'SYSDATE + 1/24'
Execute every 2 hour -- 'SYSDATE + 2/24'
Execute every 10 min. -- 'SYSDATE + 10/1440'
Execute every 30 sec. -- 'SYSDATE + 30/86400'


Remove or Delete the DBMS job:

exec dbms_job.remove(23);


Update or Change the existing job which is already created
DBMS_JOB.CHANGE procedure alters a specified job that has already been submitted to the job queue. You want to change description, timing, force, intervals etc.


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);

Parameters means:
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.

Example: Change 1 job id next date parameter to after 3 days:
EXECUTE DBMS_JOB.CHANGE(1, null, sysdate+3, null);

Update definition of job with WHAT procedure
execute DBMS_JOB.WHAT(3,Begin SEND_MESSAGE(); End;');

Changing 0r altering the next execution time with NEXT DATE procedure
exec DBMS_JOB.NEXT_DATE(4,SYSDATE+3);

Changing the Interval with dbms_job.internabl procedure
Exec DBMS_JOB.INTERVAL (job IN BINARY_INTEGER,interval IN VARCHAR2);

Assign job in RAC environment to specific instance with INSTANCE Procedure

SELECT instance_number FROM gv$instance;
EXECUTE DBMS_JOB.INSTANCE(job=>123, instance=>1);


ISUBMIT: Submit a job with a user specified job number, that you are superuser done the owner of that job.

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(42, ‘MYPROC’, SYSDATE);

Check the created jobs present in the Database or USER

SELECT job, next_date,next_sec,failures, broken,SUBSTR(what,1,40) DESCRIPTION
FROM dba_jobs;


Checked the Jobs that are currently running:

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 dba_jobs;

Which 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;