Database Reference
In-Depth Information
CHAPTER 18
■ ■ ■
DBMS_IJOB
T
he package
DBMS_IJOB
is an undocumented PL/SQL package that is called internally by
DBMS_
JOB
. By using
DBMS_IJOB
directly, the limitations inherent in
DBMS_JOB
may be overcome. Using
DBMS_IJOB
, it is possible to create and drop jobs in other schemas, to export jobs as PL/SQL
scripts, and to change the NLS environment of jobs as well as the database user who runs a job.
Execute permission on
DBMS_IJOB
is included in the role DBA. Unless the January 2009 Critical
Patch Update is installed in an ORACLE_HOME, database users with access to
DBMS_IJOB
can
circumvent auditing.
Introduction to DBMS_JOB
The package
DBMS_JOB
submits PL/SQL procedures, which shall be run at regular intervals, to
the job queue. The job queue is enabled by setting the initialization parameter
JOB_QUEUE_
PROCESSES
to a value greater than zero. The job queue is handled by the job queue coordinator
process CJQ0 and job queue slave processes (JNNN). The documented interface to the job queue is
the package
DBMS_JOB
. This package does not allow a database administrator to create, modify,
and drop jobs in foreign schemas.
DBMS_JOB
calls the undocumented package
DBMS_IJOB
to accomplish its tasks. Using
DBMS_
IJOB
directly, allows a database administrator to overcome the aforementioned limitations.
Changes to the job queue with
DBMS_IJOB
take effect when
COMMIT
is issued (same as with
DBMS_
JOB
). The data dictionary table underlying
DBA_JOBS
is
SYS.JOB$
.
DBMS_JOB
and
DBMS_IJOB
have the procedures
BROKEN
,
REMOVE
and
RUN
in common. These
procedures have identical arguments in both packages. For each of them I provide an example
that illustrates how
DBMS_IJOB
gives the DBA full control of all jobs in the database.
BROKEN Procedure
This procedure may be used to change the status of a job in any schema, thus overcoming the
limitation of
DBMS_JOB.BROKEN
. Jobs with status broken (
DBA_JOBS.BROKEN=Y
) are not run auto-
matically, but may be run manually with
DBMS_JOB.RUN
.
173
