Oracle SCHTASKS.EXE Scheduling Oracle Tips by Burleson Consulting

SCHTASKS.EXE

The SCHTASKS command was introduced in Windows XP and Windows 2003 as a more flexible and slightly more verbose replacement for the AT command.  The AT command is still available for backwards compatibility, but it is no longer the preferred command line scheduling method.

As with Windows 2000, the simplest way to schedule jobs in Windows XP and Windows 2003 is via the Scheduled Tasks Wizard; however, the SCHTASKS command provides a command line API for situations in which a command line approach is preferable.

The usage notes for the SCHTASKS command are very comprehensive and include examples as well as basic syntax.  The top level usage notes are displayed below, with examples indicating how more parameter specific usage notes can be obtained.

C:\>SCHTASKS /?

SCHTASKS /parameter [arguments]

Description:

Enables an administrator to create, delete, query, change, run and end scheduled tasks on a local or remote system. Replaces AT.exe.

Parameter List:

      /Create         Creates a new scheduled task.

    /Delete         Deletes the scheduled task(s).

    /Query          Displays all scheduled tasks.

    /Change         Changes the properties of scheduled task.

    /Run            Runs the scheduled task immediately.

    /End            Stops the currently running scheduled task.

    /?              Displays this help/usage.

Examples:

    SCHTASKS
    SCHTASKS /?
    SCHTASKS /Run /?
    SCHTASKS /End /?
    SCHTASKS /Create /?
    SCHTASKS /Delete /?
    SCHTASKS /Query  /?
    SCHTASKS /Change /?

To schedule a job that runs the c:\jobs\MyJob.bat script at 9:00 p.m. on Mondays, Tuesdays, Thursdays and Fridays; do the following commands would be used:

SCHTASKS /Create /TN MyJob /TR C:\Jobs\MyJob.bat /ST 21:00:00 /SC weekly /D MON,TUE,THU,FRI

The task will be created under current logged-on user name ("tim_hall").

Please enter the run as password for tim_hall: ******

SUCCESS: The scheduled task "MyJob" has successfully been created.

Once a task is created, it can be viewed by issuing the SCHTASKS command with no parameters:

C:\>SCHTASKS

TaskName                             Next Run Time            Status
==================================== ========================
MyJob                                21:00:00, 04/06/2004

Tasks that are no longer needed can be deleted using the /delete option:

C:\>SCHTASKS /delete /TN MyJob

WARNING: Are you sure you want to remove the task "MyJob" (Y/N )? y

SUCCESS: The scheduled task "MyJob" was successfully deleted.

C:\>SCHTASKS

INFO: There are no scheduled tasks present in the system.

C:\>

Since some of the external schedulers available on the most common operating systems have been presented, the following section will focus on the internal schedulers provided by the Oracle database.

Internal Oracle Job Scheduling

The Oracle scheduler allows jobs to be scheduled to run at a later date, or on a repeating cycle.  Information about the scheduling session?s environment is stored along with the scheduled job, allowing jobs to run in a consistent environment each time.  Scheduled jobs are placed on a job queue that is managed by a coordinator process, which periodically scans the job queue looking for jobs to execute.  When necessary, the coordinator process spawns job slaves to execute the jobs.  The basic architecture of the Oracle scheduler is shown in Figure 1.10.

Figure 1.10 ? Oracle Scheduler Architecture

The basic architecture of Oracle 9i and Oracle 10g schedulers may be similar, but the functionality and associated APIs are quite different.  The Oracle 9i scheduler is extremely basic and a little clumsy, while the Oracle 10g scheduler is packed with features allowing job scheduling to be as simple or complicated as desired.  The original scheduler API has been retained in Oracle 10g for backwards compatibility, and it is this scheduler that will be focused on first.

Oracle 9i Scheduler Overview

In Oracle9i, the job_queue_processes parameter sets an upper limit for the number of job slaves that can be spawned to execute jobs.  The value assigned must be between 0-1000; with zero being the default.  When the value is greater than zero, the job coordinator background process (cjq0) is started and remains active until the instance is shut down or the parameter is set to a value of zero.  The job_queue_processes parameter is dynamic, so it can be set using the ALTER SYSTEM command, as shown in the following code.

SQL> alter system set job_queue_processes=10;

System altered.

SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
job_queue_processes                  integer     10

Each node in a Real Application Cluster (RAC) installation can have different settings for the job_queue_processes parameter.

The job coordinator periodically scans the job$ table looking for jobs to execute.  When it finds a job to execute, it spawns a job queue slave process (j000 to j999) and passes the job to it.  Once the job slave has completed executing a job, it requests another job from the job coordinator.  If a job is not available, the job slave switches to an idle state, periodically requesting more work.  If the job slave remains idle for too long, it terminates and is re-spawned as needed.

For a long time, the dbms_job package has been the focus of Oracle?s internal job scheduling, providing a simple API that allows jobs to be created, modified and deleted.  The usage of the dbms_job package is included in more detail in Chapter 2; however, the following example gives an indication of how a simple job can be scheduled using it:

VARIABLE l_job NUMBER;

BEGIN
  DBMS_JOB.submit (
    job       => :l_job,
    what      => 'BEGIN NULL; /* Do Nothing */ END;',
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1 /* 1 Day */');

  COMMIT;
END;
/

PRINT l_job

In the above example, an anonymous PL/SQL block is scheduled to run instantly, then rerun every 24 hours indefinitely.  A quick look at the contents of the anonymous block reveals that it doesn?t actually do anything, but it could easily contain DML or a database procedure call.

If the dbms_job package was the past, the dbms_scheduler package is the future and is the focus of the next section.

 

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall. You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.