Archive

Archive for the ‘DBMS_JOB’ Category

DBMS_JOB package

December 13, 2009 1 comment

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.sq

SNP 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;
Advertisements
Categories: DBMS_JOB Tags: