Introduction to Job Scheduling
Job scheduling is an important but often
overlooked part of every database environment.
Even the simplest systems require
scheduled jobs such as data loads, data
extraction, report generation, backups and
When scheduling these tasks, the DBA or
developer has to make a choice whether to use an
external scheduler provided by the operating
system or an internal scheduler provided by the
This chapter introduces both external and
internal schedulers, beginning with the external
schedulers available in UNIX, Linux, and Windows
Then it will proceed to examine Oracle’s
internal scheduler and cover how to schedule
jobs with the Oracle
Fundamentals of Job Scheduling
Job scheduling is as old as data processing
itself, dating back to the 1960s with the JES
(job entry system) on the IBM mainframes, back
in the days of punched cards.
Job scheduling is not a new technology,
although it is new to Oracle.
Very few Oracle systems are random in nature.
Most business databases are driven by
business cycles like the inline processing day,
nightly batch processing and end-of-week
reporting as well as monthly scheduled tasks.
Even within the realm of Oracle database
administration, there is a need for regularly
Every 5 minutes
– check alert log for error messages, check
for new dump or trace files, verify that
Oracle processes are running
– perform hot backups, check OS disk space
– clean out elderly redo log files, perform
predictive analytics to seek important
trends, collect table growth reports
– Check for database fragmentation, apply
It is databases with this sort of repeating
cycles that are ideal for Oracle job scheduling.
To understand how scheduled jobs work,
start with an overview of external vs. internal
job scheduling and see the differences.
UNIX Job Scheduling (cron) vs dbms_scheduler
Back before the advent of the
dbms_jobs package (the predecessor
dbms_scheduler), all Oracle tasks
were scheduled at the OS layer using the cron
deamon with crontab files.
While cron reliably executes jobs when
the server is up, effective job chain management
relies on sophisticated user-written shell
scripts to manage internal job chain
These scripts were written in Korn shell (ksh),
Bourne Again Shell (bash) or C Shell (csh)
command languages, and job chaining activities
like checking step status and e-mailing failures
have to be coded manually.
For full details on the nuances of Oracle
shell scripting, see Jon Emmons’ book “Linux
Shell Scripting for Oracle” by
In sum, using cron for Oracle job scheduling has
several important drawbacks:
It is difficult to code complex job chains –
Maintaining job step status and
restartability requires significant shell
language coding skills.
No centralized job location - Each
UNIX/Linux user may have their own
crontab file and it can be
difficult to locate and manage OS scheduled
Does not detect missed jobs – If the server
is down during the time a job is executing,
the OS will not inform the DBA upon server
On the other hand, the
dbms_scheduler package is not
perfect either and it also has some
deficiencies, foremost of which is the inability
to easily communicate with the OS.
For this reason, cron is still used for
many OS-intensive jobs, such as scheduled jobs
to clean out the archived redo log directory.
Other deficiencies include:
Difficult to shell out to the OS – Some
Oracle jobs require information from the
server environment (disk space, RAM usage)
and these are not easy to collect within
Also, special techniques must be used
to send signals to the OS such as when one
needs to send an email or pager alert for a
Cryptic syntax – The syntax for scheduling
complex jobs requires detailed knowledge for
Oracle’s internal date-time formatting
masks; performing error checking and
restarts requires complex syntax.
While neither approach is perfect, these two
approaches together allow the Oracle
professional to create complex conditional job
streams and automate complex nightly processing
with relative ease.
Now take a closer look at the fundamentals of
job scheduling for Oracle.
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from