Oracle DBMS JOBS

1)linuxLevel – crontab here is the Oracle server level

2)from 9i to 10g dbms jobs

3)Dbms scheduler from 10g

4)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.

5)Source: {ORACLE_HOME}/rdbms/admin/dbmsjob.sq

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

7)The INIT.ORA initialization file contains 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.

The person who submits a job to the job queue is considered the job’s owner.

8)Views

dba_jobs      

all_jobs      

user_jobs

dba_jobs_running      

all_jobs_running      

user_jobs_running

9)job timing(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

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

explanation

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.

practicals

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

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

 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;

Code:

EXECUTE  DBMS_JOB.INSTANCE(job=>123, instance=>1);

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;

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:

 Code:

SELECT  job, next_date,

next_sec,

failures,

broken,

SUBSTR(what,1,40) DESCRIPTION

FROM user_jobs;

Jobs that are currently running:

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