Skip Headers
Oracle® Database Administrator's Guide
11g Release 1 (11.1)

Part Number B28310-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

Monitoring and Managing the Scheduler

The following sections discuss how to monitor and manage the Scheduler:

Viewing the Currently Active Window and Resource Plan

You can view the currently active window and the plan associated with it by issuing the following statement:


WINDOW_NAME                    RESOURCE_PLAN
------------------------------ --------------------------
MY_WINDOW10                    MY_RESOURCEPLAN1

If there is no window active, you can view the active resource plan by issuing the following statement:


Finding Information About Currently Running Jobs

You can check a job's state by issuing the following statement:


JOB_NAME                       STATE
------------------------------ ---------
MY_EMP_JOB1                    DISABLED

In this case, you could enable the job using the ENABLE procedure. Table 28-1 shows the valid values for job state.

Table 28-1 Job States

Job State Description


The job is disabled.


The job is scheduled to be executed.


The job is currently running.


The job has completed, and is not scheduled to run again.


The job was scheduled to run once and was stopped while it was running.


The job is broken.


The job was scheduled to run once and failed.

retry scheduled

The job has failed at least once and a retry has been scheduled to be executed.


The job was scheduled to run once and completed successfully.


The job is of type chain and has no steps running, no steps scheduled to run, and no event steps waiting on an event, and the chain evaluation_interval is set to NULL. No progress will be made in the chain unless there is manual intervention.

You can check the progress of currently running jobs by issuing the following statement:


Note that, for the column CPU_USED to show valid data, the initialization parameter RESOURCE_LIMIT must be set to true.

You can find out information about a job that is part of a running chain by issuing the following statement:


You can check whether the job coordinator is running by searching for a process of the form cjqNNN.

See Also:

Oracle Database Reference for details regarding the *_SCHEDULER_RUNNING_JOBS and DBA_SCHEDULER_JOBS views

Monitoring and Managing Window and Job Logs

Logs have a new entry for each event that occurs so that you can track historical information. This is different from a queue, where you only want to track the latest status for an item. There are logs for jobs, job runs, and windows.

Job activity is logged in the *_SCHEDULER_JOB_LOG views. Altering a job is logged with an operation of UPDATE. Dropping a job is logged in these views with an operation of DROP.

See Also:

Oracle Database Reference for details on the *_SCHEDULER_JOB_LOG views and other Scheduler log views.

Job Logs

To see the contents of the job log, query the DBA_SCHEDULER_JOB_LOG view. An example is the following statement, which shows what happened for past job runs:


--------        ---------     -----
MY_JOB13        CREATE        SYS
MY_JOB14        CREATE        OE
MY_JOB1         CREATE        SCOTT
MY_JOB14        RUN           OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        BROKEN        OE
MY_JOB14        DROP          OE

When logging_level for a job is set to LOGGING_FULL, the additional_info column of the job log contains the before and after values of the modified attribute on update operations, and contains the values of all attributes on drop operations. This enables you to trace backwards from the current job state to the state of the job on previous job runs.

Job Run Details

To further analyze each job run—why it failed, what the actual start time was, how long the job ran, and so on—query the DBA_SCHEDULER_JOB_RUN_DETAILS view. As an example, the following statement illustrates the status for my_job14:

select log_id, job_name, status, 
to_char(log_date, 'DD-MON-YYYY HH24:MI') log_date 
from dba_scheduler_job_run_details
where job_name = 'MY_JOB14';

    LOG_ID JOB_NAME               STATUS       LOG_DATE
---------- ---------------------- ------------ -----------------
        69 MY_JOB14               SUCCEEDED    02-JUN-2005 03:14
       124 MY_JOB14               SUCCEEDED    03-JUN-2005 03:15
       133 MY_JOB14               FAILURE      04-JUN-2005 03:00
       146 MY_JOB14               FAILURE      05-JUN-2005 03:01

For every row in SCHEDULER_JOB_LOG that is of operation RUN, RETRY_RUN, or RECOVERY_RUN, there will be a corresponding row in the *_JOB_RUN_DETAILS view with the same LOG_ID. LOG_DATE contains the timestamp of the entry, so sorting by LOG_DATE should give you a chronological picture of the life of a job.

Controlling Job Logging

You can control the amount of logging that the Scheduler performs on jobs at either a class or job level. Normally, you will want to control jobs at a class level as this offers a full audit trail. To do this, use the logging_level attribute in the CREATE_JOB_CLASS procedure.

For each new class, the creator of the class must specify what the logging level is for all jobs in that class. The three possible options are:


    No logging will be performed for any jobs in this class.


    The Scheduler will write detailed information to the job log for all runs of each job in this class.


    In addition to recording every run of a job, the Scheduler will record all operations performed on all jobs in this class. In other words, every time a job is created, enabled, disabled, altered, and so on will be recorded in the log.

By default, only job runs are recorded. For job classes that have very short and highly frequent jobs, the overhead of recording every single run might be too much and you might choose to turn the logging off. You might, however, prefer to have a complete audit trail of everything that happened to the jobs in a specific class, in which case you need to turn on full logging for that class.

The second way of controlling the logging level is on an individual job basis. You should keep in mind, however, that the log in many cases is used as an audit trail, thus if you want a certain level of logging, the individual job creator must not be able to turn logging off. The class-specific level is, therefore, the minimum level at which job information will be logged. A job creator can only turn on more logging for an individual job, not less.

This functionality is provided for debugging purposes. For example, if the class-specific level is set to record job runs and the job-specific logging is turned off, the Scheduler will still log the runs. If, on the other hand, the job creator turns on full logging and the class-specific level is set to record runs only, all operations on this individual job will be logged. This way, an end user can test his job by turning on full logging.

To set the logging level of an individual job, you must use the SET_ATTRIBUTE procedure on that job. For example, to turn on full logging for a job called mytestjob, issue the following statement:

   'mytestjob', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);

See Also:

Oracle Database PL/SQL Packages and Types Reference for detailed information about the CREATE_JOB_CLASS and SET_ATTRIBUTE procedures and "Task 2E: Setting Scheduler Attributes"

Window Logs

A window log has an entry for each time you do the following:

  • Create a window

  • Drop a window

  • Open a window

  • Close a window

  • Overlap windows

  • Disable a window

  • Enable a window

There are no logging levels for window logging, but every window operation will automatically be logged by the Scheduler.

To see the contents of the window log, query the DBA_SCHEDULER_WINDOW_LOG view. The following statement shows sample output from this view:


---------- ---------- ----------------- ------------------------------
         1 10/01/2004 WEEKNIGHT_WINDOW  CREATE
         2 10/01/2004 WEEKNIGHT_WINDOW  UPDATE
         3 10/01/2004 WEEKNIGHT_WINDOW  UPDATE
         4 10/01/2004 WEEKEND_WINDOW    CREATE
         5 10/01/2004 WEEKEND_WINDOW    UPDATE
         6 10/01/2004 WEEKEND_WINDOW    UPDATE
        22 10/06/2004 WEEKNIGHT_WINDOW  OPEN
        25 10/06/2004 WEEKNIGHT_WINDOW  CLOSE
        26 10/06/2004 WEEKNIGHT_WINDOW  OPEN
        29 10/06/2004 WEEKNIGHT_WINDOW  CLOSE

The DBA_SCHEDULER_WINDOWS_DETAILS view provides information about every window that was active and is now closed (completed). The following statement shows sample output from that view:


---------- ---------------- ------------------------------------ -------------
        25 WEEKNIGHT_WINDOW 06-OCT-04 PM PST8PDT +000 01:02:32
        29 WEEKNIGHT_WINDOW 06-OCT-04 PM PST8PDT +000 03:02:00

Notice that log IDs correspond in both of these views, and that in this case the rows in the DBA_SCHEDULER_WINDOWS_DETAILS view correspond to the CLOSE operations in the DBA_SCHEDULER_WINDOW_LOG view.

Purging Logs

To prevent job and window logs from growing indiscriminately, use the SET_SCHEDULER_ATTRIBUTE procedure to specify how much history (in days) to keep. Once per day, the Scheduler automatically purges all log entries that are older than the specified history period from both the job log and the window log. The default history period is 30 days. For example, to change the history period to 90 days, issue the following statement:


Some job classes are more important than others. Because of this, you can override this global history setting by using a class-specific setting. For example, suppose that there are three job classes (class1, class2, and class3), and that you want to keep 10 days of history for the window log, class1, and class3, but 30 days for class2. To achieve this, issue the following statements:


You can also set the class-specific history when creating the job class.

Note that log entries pertaining to steps of a chain run are not purged until the entries for the main chain job are purged.

Purging Logs Manually

The PURGE_LOG procedure enables you to manually purge logs. As an example, the following statement purges all entries from both the job and window logs:


Another example is the following, which purges all entries from the jog log that are older than three days. The window log is not affected by this statement.

DBMS_SCHEDULER.PURGE_LOG(log_history => 3, which_log => 'JOB_LOG');

The following statement purges all window log entries older than 10 days and all job log entries older than 10 days that relate to job1 and to the jobs in class2:

DBMS_SCHEDULER.PURGE_LOG(log_history => 10, job_name => 'job1, sys.class2');

Changing Job Priorities

You can change job priorities by using the SET_ATTRIBUTE procedure. Job priorities must be in the range 1-5 with 1 being the highest priority. For example, the following statement changes the job priority for my_job1 to a setting of 1:

   name           =>   'my_emp_job1',
   attribute      =>   'job_priority',
   value          =>   1);

You can verify that the attribute was changed by issuing the following statement:


JOB_NAME                       JOB_PRIORITY
------------------------------ ------------
MY_EMP_JOB                                3
MY_EMP_JOB1                               1
MY_NEW_JOB1                               3
MY_NEW_JOB2                               3
MY_NEW_JOB3                               3

See Also:

Oracle Database PL/SQL Packages and Types Reference for detailed information about the SET_ATTRIBUTE procedure

Monitoring Running Chains

You can check the state of a running chain by querying the *_SCHEDULER_RUNNING_CHAINS views. The results contain a row describing the current state of every step in every running instance of a chain. For example, the following statement displays the state of all steps in the running job MY_CHAIN_JOB. It also shows the state of all steps of any nested chain jobs that are running or have completed.


See "Using Chains" for more information regarding chains.

Managing Scheduler Security

You should grant the CREATE JOB system privilege to regular users who need to be able to use the Scheduler to schedule and run jobs. You should grant MANAGE SCHEDULER to any database administrator who needs to be able to manage system resources. Granting any other Scheduler system privilege or role should not be done without great caution. In particular, the CREATE ANY JOB system privilege and the SCHEDULER_ADMIN role, which includes it, are very powerful because they allow execution of code as any user. They should only be granted to very powerful roles or users.

A particularly important issue from a security point of view is handling external jobs. Only users that need to run jobs outside of the database should be allowed to do so. You must grant the CREATE EXTERNAL JOB system privilege to those users. See "Creating Remote External Jobs" for further information. Security for the Scheduler has no other special requirements. See Oracle Database Security Guide for details regarding security.


When upgrading from Oracle Database 10g Release 1 to 10g Release 2 or later, CREATE EXTERNAL JOB is automatically granted to all users and roles that have the CREATE JOB privilege. Oracle recommends that you revoke this privilege from users that don't need it.