Oracle PL/SQL for DBAs

Why Schedule Jobs from Within Oracle?

Oracle DBAs who need to schedule a job for execution—either at a specific, future time or at regularly scheduled intervals—generally have two options from which to choose:

Use the operating system scheduler

Execute the program or code segment from an operating system-specific utility such as cron in Unix or AT in Microsoft Windows. These tools let you execute an operating system-specific command as needed. You can even run an Oracle job from the frontend, usually by constructing it as a SQL script and then running it through SQL*Plus as a command-line execution.

Use the database scheduler from within Oracle

Schedule the operation as a job in the database to be run either one time or repeatedly using DBMS_SCHEDULER. This database job runs within the context of the database, not the operating system.

Given that you have the ability to schedule jobs in the operating system using standard operating system utilities, why would you even consider scheduling them inside the database? There are several compelling reasons:

Database dependence

An operating system job runs regardless of whether the database is available. So what happens if the job to collect optimizer statistics starts at its scheduled time but the database is down for some reason? The job fails, which may then lead to a variety of unpleasant consequences. Collecting statistics is a rather benign example; in other cases, this scenario could present more serious ...