MANAGING JOB QUEUES

MANAGING JOB QUEUES

DBMS_JOB Package:
There are no database privileges associated with using job queues. Any
user who can execute the job queue procedures can use the job queue.

Procedures:
SUBMIT: Submits a job to the job queue.
REMOVE: Removes a specified job from the job queue.
CHANGE: Alters a specified job that has already been submitted to the job
queue. You can alter the job description, the time at which the
job will be run, or the interval between executions of the job.
WHAT: Alters the job description for a specified job.
NEXT_DATE :Alters the next execution time for a specified job.
INTERVAL: Alters the interval between executions for a specified job.
BROKEN: Sets or resets the job broken flag. If a job is marked as broken,
Oracle does not attempt to execute it.

RUN: Forces a specified job to run.

Submitting a job to the Job Queue
To submit a new job to the job queue, use the SUBMIT procedure.
Parameter:
JOB : An output parameter. This is the identifier assigned to the job
you are creating. You must use this job number whenever you
want to alter or remove the job.
WHAT : This is the PL/SQL code you want to have executed.
NEXT_DATE : This is the next date when the job will be run. The default value
is SYSDATE.

INTERVAL : This is 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.

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.

Ex: submit a new job to the job queue, then prints the job number.
The job is run every 24 hours.

VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT(:jobno,'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',''HR'',''EMPLOYEES'',''ESTIMATE'', NULL, 50);',SYSDATE,'SYSDATE + 1');
COMMIT;
END;
/

PRINT jobno
JOBNO
----------
14144

For the submitted job to run, you must issue a COMMIT statement immediately after the DBMS_JOB.SUBMIT statement.

Job Environment
When you submit a job to the job queue or alter a job’s definition, Oracle records:
The current user
The user submitting or altering a job
The current schema (may be different from current user or submitting user if
ALTER SESSION SET CURRENT_SCHEMA statement has been issued)

NLS_LANGUAGE
NLS_TERRITORY
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_SORT

Oracle restores all of these environment characteristics every time a job is executed.
NLS_LANGUAGE and NLS_TERRITORY parameters determine the defaults for unspecified NLS parameters.

You can change a job’s environment by using the DBMS_SQL package and the ALTER SESSION statement.

Job Owner
When you submit a job to the job queue, Oracle identifies you as the owner of the
job. Only a job’s owner can alter the job, force the job to run, or remove the job from
the queue.

Job Number
A queued job is identified by its job number. When you submit a job, its job number
is automatically generated from the JOBSEQ sequence owned by user SYS.

Job Definition
The job definition is the PL/SQL code specified in the WHAT parameter of the SUBMIT
procedure. Normally, the job definition is a single call to a procedure. The procedure
call can have any number of parameters.

In the job definition, use two single quotation marks around
strings. Always include a semicolon at the end of the job definition.

examples of valid job definitions:
'myproc(''10-JAN-99'', next_date, broken);'
'scott.emppackage.give_raise(''JFEE'', 3000.00);'
'dbms_job.remove(job);'

Running a job from a job is not supported.
"ORA-32317 cannot run a job from another job" error message in the alert file.

Job Execution Interval
If a job should be executed periodically at a set interval, use a date expression
similar to 'SYSDATE + 7' in the INTERVAL parameter.

The INTERVAL date function is evaluated immediately before a job is executed.
When the job completes successfully, the date calculated from INTERVAL becomes
the new NEXT_DATE. For example, if you set the execution interval to 'SYSDATE +
7' on Monday, but for some reason (such as a network failure) the job is not
executed until Thursday, 'SYSDATE + 7' then executes every Thursday, not
Monday. If the INTERVAL date function evaluates to NULL and the job completes
successfully, the job is deleted from the queue.

Database Links and Jobs:
If you submit a job that uses a database link, the link must include a username and
password. Anonymous database links will not succeed.

How Jobs Execute
Jnnn processes execute jobs.
To execute a job, the process creates a session to run the job.
When a Jnnn process runs a job, the job is run in the same environment in which it was submitted and with the owner’s default privileges.

When you force a job to run using the procedure DBMS_JOB.RUN, the job is run by your user process and with your default privileges only.
Privileges granted to you through roles are unavailable.
You must be explicitly granted the necessary object privileges for all objects referenced within the job definition.

Job Queue Locks:
Oracle uses job queue locks to ensure that a job is executed in only one session at a time.
When a job is being run, its session acquires a job queue (JQ) lock for that job.

You can use the locking views in the data dictionary to examine information about locks currently held by sessions.

The following query lists the session identifier, lock type, and lock identifiers for all sessions holding JQ locks:
SELECT SID, TYPE, ID1, ID2 FROM V$LOCK WHERE TYPE = 'JQ';

SID TY ID1 ID2
--------- -- --------- ---------
12 JQ 0 14144

ID1 column is always 0 for JQ locks.
ID2 column is the job number of the job the session is running.

Job Execution Errors
When a job fails, information about the failure is recorded in a trace file and the alert log.
ORA-12012 and includes the job number of the failed job.

If a job returns an error while Oracle is attempting to execute it, Oracle tries to execute it again.

The first attempt is made after one minute, the second attempt after two minutes, the third after four minutes, and so on, with the interval doubling
between each attempt.

If the job fails 16 times, Oracle automatically marks the job as broken and no longer tries to execute it.

Removing a job from the Job Queue
To remove a job from the job queue,
BEGIN
DBMS_JOB.REMOVE(14144);
END;
/

Restrictions:
You can remove currently executing jobs from the job queue.
The job will not be interrupted, and the current execution will be completed.

You can remove only jobs you own.
If you try to remove a job that you do not own, you receive a message that states the job is not in the job queue.

Altering a Job
To alter a job that has been submitted to the job queue, use the procedures
CHANGE,
WHAT,
NEXT_DATE,
INTERVAL


Restriction:
You can alter only jobs that you own.
If you try to alter a job that you do not own, you receive a message that states the job is not in the job queue.

CHANGE:
You can alter any of the user-definable parameters associated with a job by calling the DBMS_JOB.CHANGE procedure.

ex:
BEGIN
DBMS_JOB.CHANGE(14144, NULL, NULL, 'SYSDATE + 3');
END;
/

If you specify NULL for WHAT, NEXT_DATE, or INTERVAL when you call the
procedure DBMS_JOB.CHANGE, the current value remains unchanged.

When you change a job’s definition using the WHAT parameter in the procedure DBMS_JOB.CHANGE, Oracle records
your current environment. This becomes the new environment for the job.

WHAT:
You can alter the definition of a job by calling the DBMS_JOB.WHAT procedure.

ex:
BEGIN
DBMS_JOB.WHAT(14144,
'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',
''HR'', ''DEPARTMENTS'',
''ESTIMATE'', NULL, 50);');
END;
/

When you execute the procedure DBMS_JOB.WHAT, Oracle records your current environment. This becomes the new
environment for the job.

NEXT_DATE:
You can alter the next execution time for a job by calling the DBMS_JOB.NEXT_DATE procedure,

BEGIN
DBMS_JOB.NEXT_DATE(14144, SYSDATE + 4);
END;
/

INTERVAL:
BEGIN
DBMS_JOB.INTERVAL(14144, 'NULL');
END;
/

In this case, the job will not run again after it successfully executes and it will be
deleted from the job queue.

Broken Jobs
A job is labeled as either broken or not broken.
Oracle does not attempt to run broken jobs.

you can force a broken job to run by calling the procedure DBMS_JOB.RUN.

How a Job Becomes Broken:

When you submit a job it is considered not broken.
There are two ways a job can break:
1) Oracle has failed to successfully execute the job after 16 attempts.
2) You have marked the job as broken, using the procedure DBMS_JOB.BROKEN:

BEGIN
DBMS_JOB.BROKEN(14144, TRUE);
END;
/


Once a job has been marked as broken, Oracle will not attempt to execute the job
until you either mark the job as not broken, or force the job to be executed by calling
the procedure DBMS_JOB.RUN.

The following example marks job 14144 as not broken and sets its next execution
date to the following Monday:
BEGIN
DBMS_JOB.BROKEN(14144, FALSE, NEXT_DAY(SYSDATE, 'MONDAY'));
END;
/

Restriction:
You can mark as broken only jobs that you own.

Running Broken Jobs
If a problem has caused a job to fail 16 times, Oracle marks the job as broken.
1) Forcing the job to run by calling DBMS_JOB.RUN
2) Marking the job as not broken by calling DBMS_JOB.BROKEN and waiting for Oracle to execute the job

If you force the job to run by calling the procedure DBMS_JOB.RUN, Oracle runs the
job immediately. If the job succeeds, then Oracle labels the job as not broken and
resets its count of the number of failed executions for the job to zero.
Once you reset a job’s broken flag (by calling either RUN or BROKEN), job execution
resumes according to the scheduled execution intervals set for the job.


Forcing a Job to Execute
To force a job to execute immediately, use the procedure RUN in the DBMS_JOB package.
When you run a job using DBMS_JOB.RUN, Oracle recomputes the next execution date.


The following statement runs job 14144 in your session and recomputes the next execution date:
BEGIN
DBMS_JOB.RUN(14144);
END;
/

When you force a job to run, the job is executed in your current session. Running the job reinitializes your session’s packages.

Restrictions:
You can only run jobs that you own. If you try to run a job that you do not own,
you receive a message that states the job is not in the job queue.
The procedure RUN contains an implicit commit. Once you execute a job using
RUN, you cannot roll back.

Terminating a Job
You can terminate a running job by marking the job as broken, identifying the session running the job, and disconnecting that session.

You should mark the job as broken, so that Oracle does not attempt to run the job again.

After you have identified the session running the job (using V$SESSION or V$LOCK, as shown earlier), you can disconnect the session using the SQL statement ALTER SYSTEM.

Viewing Job Queue Information
DBA_JOBS
DBA view describes all the jobs in the database.

ALL_JOBS
ALL view describes all jobs that are accessible to the current user.

USER_JOBS
USER view describes all jobs owned by the current user.

DBA_JOBS_RUNNING
Lists all jobs in the database that are currently running.
This view can be joined with V$LOCK to identify jobs that have locks.


________________________________________________________________________________
Displaying Information About a Job:
________________________________________________________________________________

Query creates a listing of the job number, next execution time, failure count, and broken status for each job you have submitted:

SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM DBA_JOBS;

JOB NEXT_DATE NEXT_SEC FAILURES B
------- --------- -------- -------- -
9125 01-JUN-01 00:00:00 4 N
1414 24-OCT-01 16:35:35 0 N
9127 01-JUN-01 00:00:00 16 Y


________________________________________________________________________________
Displaying Information About Running Jobs
________________________________________________________________________________
The following query lists the session identifier, job number, user who submitted the job,
and the start times for all currently running jobs:

SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC FROM DBA_JOBS_RUNNING r, DBA_JOBS j
WHERE r.JOB = j.JOB;

SID JOB LOG_USER THIS_DATE THIS_SEC
----- ---------- ------------- --------- --------
12 14144 HR 24-OCT-94 17:21:24
25 8536 QS 24-OCT-94 16:45:12