Scheduling Jobs in the Database
Operating systems such as UNIX/Windows provide a facility called a cron program, which is used to execute certain tasks at specified times and/or repeated at regular intervals at the operating system level. This facility is very useful to run commands on a regularly scheduled basis, to perform periodic operations, to run programs at night for fear of slowing down other users if it were run during the day, and so forth. The Cron is executed at the operating system level.
You need a similar way of scheduling jobs in the database. This article explains job scheduling in the database with an example in detail.
Sometimes, you need to execute certain operations at predefined times and/or repeated at regular intervals. This happens exclusively in the database.
If you use cron to schedule the jobs for executing database-related tasks, you need to embed passwords in script files. Most operating systems' implemented facility has a number of drawbacks; for example, it won't check whether the database is running or not when the job starts. How do you prevent the job running again if something is seriously wrong? You need a facility to solve these kinds of drawbacks.
Oracle incorporated a new facility to run stored procedures, or "jobs," at specified times and frequencies as background processes. The major advantage of this new facility is that it solves most of the previously mentioned problems in a simple, straightforward manner. The database itself is responsible for invoking the scheduled procedures, so if the database is down at the scheduled time, no matter; the procedure will be started when the database is restarted. No need to reveal the critical password. The procedure runs with the privileges of the user that schedules it. If you don't want it run again, simply set the job status to "broken." It won't be run again until the status is changed.
Oracle 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. Oracle provides a built-in package DBMS_JOB to schedule the jobs. The DBMS_JOB package is actually an API into an Oracle subsystem known as the job queue. The DBMS_JOB package is created when the Oracle database is installed.
The DBMS_JOB package has EXECUTE privileges with public access. All Oracle users can reference and use this package.
There are several data dictionary views that display information about the Oracle job queue. These are called DBA_JOBS, USER_JOBS, and DBA_JOBS_RUNNING.
Before you can schedule job execution, you need to make sure that the database is set up to process jobs. One important thing is that the job queue must have its dedicated background processes started to process the jobs. This is accomplished by setting an initialization parameter, job_queue_processes, in the INIT.ORA file for the database.
If the job is not running properly once it is scheduled, the first thing you need to do is check the number of job queue processes available to execute jobs. For that, you check the job_queue_processes initialization parameter. The following SQL statement displays the job_queue_processes initialized value.
SQL> show parameter job_queue_processes NAME TYPE VALUE job_queue_processes integer 100
This parameter should be set higher than the maximum number of simultaneous jobs you expect to run. The maximum limit is 1000.
If this parameter is set to 0, jobs will not be processed. If you want to start processing jobs, the parameter can be set dynamically with the ALTER SYSTEM command.
ALTER SYSTEM SET job_queue_processes = 100;
This command enables job queue processing by starting the CJQ0 process. Similarly, you can disable all job queue processing and stop the CJQ0 process by setting the parameter to 0.
ALTER SYSTEM SET job _queue_processes = 0;
Changes to the parameter with the ALTER SYSTEM command will be in effect only until the database is restarted. You should make changes in init or spfile if you want the changes to be permanent.
The parameter is:
JOB_QUEUE_PROCESSES = n
where n is a number between 1 and 36.
The following is the list of procedures defined for the DBMS_JOB packages.
- BROKEN: Marks the job as broken; do not re-execute
- CHANGE: Changes job parameters that can be set by user
- CHECK_PRIVS: Checks the privileges
- INTERVAL: Changes execution interval for job
- ISUBMIT: Submits a new job specifying job number
- NEXT_DATE: Changes next execution date for the job
- REMOVE: Removes existing job from the queue
- RUN: Runs the job immediately in the current session
- SUBMIT: Submits a new job, obtaining a new job number
- USER_EXPORT: Creates text of call to recreate a job
- WHAT: Changes PL/SQL executed for job