Oracle Built-in Packages

Chapter 13. Job Scheduling in the Database

The DBMS_JOB package is actually an API into an Oracle subsystem known as the job queue . The Oracle job queue allows for the scheduling and execution of PL/SQL routines (jobs) at predefined times and/or repeated job execution at regular intervals. The DBMS_JOB package provides programs for submitting and executing jobs, changing job execution parameters, and removing or temporarily suspending job execution. This package is the only interface with the Oracle job queue.

DBMS_JOB is used to schedule many different types of tasks that can be performed in PL/SQL and that require regular execution. The job queue is used extensively by Oracle replication facilities, and was originally developed for the purpose of refreshing Oracle snapshots. DBMS_JOB is often used by DBAs to schedule regular maintenance activities on databases, typically during periods of low usage by end users. It can similarly be used by applications to schedule large batch operations during off hours. The job queue can also be used to start up service programs that listen on database pipes and respond to service requests by user sessions.

Getting Started with DBMS_ JOB

The DBMS_JOB package is created when the Oracle database is installed. The dbmsjob.sql script (found in the built-in packages source code directory, as described in Chapter 1) contains the source code for this package’s specification. This script is called by catproc.sql, which is normally run immediately ...