|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
The following sections discuss how to monitor and manage the Scheduler:
You can view the currently active window and the plan associated with it by issuing the following statement:
SELECT WINDOW_NAME, RESOURCE_PLAN FROM DBA_SCHEDULER_WINDOWS WHERE ACTIVE='TRUE'; 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:
SELECT * FROM V$RSRC_PLAN;
You can check a job's state by issuing the following statement:
SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_EMP_JOB1'; JOB_NAME STATE ------------------------------ --------- MY_EMP_JOB1 DISABLED
In this case, you could enable the job using the
ENABLE procedure. Table 30-1 shows the valid values for job state.
Table 30-1 Job States
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.
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
You can check the progress of currently running jobs by issuing the following statement:
SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;
Note that, for the column
CPU_USED to show valid data, the initialization parameter
RESOURCE_LIMIT must be set to
SELECT * FROM DBA_SCHEDULER_JOB_DESTS;
You can find out information about a job that is part of a running chain by issuing the following statement:
SELECT * FROM ALL_SCHEDULER_RUNNING_CHAINS WHERE JOB_NAME='MY_JOB1';
You can check whether the job coordinator is running by searching for a process of the form
The Scheduler supports two kinds of logs: the job log and the window log.
You can view information about job runs, job state changes, and job failures in the job log. The job log is implemented as the following two data dictionary views:
You can control the amount of logging that the Scheduler performs on jobs at both the job class and individual job level. Normally, you control logging at the class level, as this offers you more control over logging for the jobs in the class.
See "Viewing the Job Log" for definitions of the various logging levels and for information about logging level precedence between jobs and their job class. By default, the logging level of job classes is
LOGGING_RUNS, which causes all job runs to be logged.
You can set the
logging_level attribute when you create the job class, or you can use the
SET_ATTRIBUTE procedure to change the logging level at a later time. The following example sets the logging level of jobs in the
myclass1 job class to
LOGGING_FAILED_RUNS, which means that only failed runs are logged. Note that all job classes are in the
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( 'sys.myclass1', 'logging_level', DBMS_SCHEDULER.LOGGING_FAILED_RUNS); END; /
You must be granted the
SCHEDULER privilege to set the logging level of a job class.
"Viewing the Job Log" for more detailed information about the job log and for examples of queries against the job log views
Oracle Database Reference for details on the job log views.
Oracle Database PL/SQL Packages and Types Reference for detailed information about the
"Setting Scheduler Preferences" for information about setting retention for log entries
The Scheduler makes an entry in the window log each time that:
You create or drop a window
A window opens
A window closes
You enable or disable a window
There are no logging levels for window activity logging.
To see the contents of the window log, query the
DBA_SCHEDULER_WINDOW_LOG view. The following statement shows sample output from this view:
SELECT log_id, to_char(log_date, 'DD-MON-YY HH24:MM:SS') timestamp, window_name, operation FROM DBA_SCHEDULER_WINDOW_LOG; LOG_ID TIMESTAMP WINDOW_NAME OPERATION ---------- -------------------- ----------------- -------- 4 10/01/2004 15:29:23 WEEKEND_WINDOW CREATE 5 10/01/2004 15:33:01 WEEKEND_WINDOW UPDATE 22 10/06/2004 22:02:48 WEEKNIGHT_WINDOW OPEN 25 10/07/2004 06:59:37 WEEKNIGHT_WINDOW CLOSE 26 10/07/2004 22:01:37 WEEKNIGHT_WINDOW OPEN 29 10/08/2004 06:59:51 WEEKNIGHT_WINDOW CLOSE
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:
SELECT LOG_ID, WINDOW_NAME, ACTUAL_START_DATE, ACTUAL_DURATION FROM DBA_SCHEDULER_WINDOW_DETAILS; LOG_ID WINDOW_NAME ACTUAL_START_DATE ACTUAL_DURATION ---------- ---------------- ------------------------------------ --------------- 25 WEEKNIGHT_WINDOW 06-OCT-04 10:02.48.832438 PM PST8PDT +000 01:02:32 29 WEEKNIGHT_WINDOW 07-OCT-04 10.01.37.025704 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
Oracle Database Reference for details on the window log views.
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 (
class3), and that you want to keep 10 days of history for the window log,
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
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
DBMS_SCHEDULER.PURGE_LOG(log_history => 10, job_name => 'job1, sys.class2');
You should grant the
JOB system privilege to regular users who need to be able to use the Scheduler to schedule and run jobs. You should grant
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
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. Security for the Scheduler has no other special requirements. See Oracle Database Security Guide for details regarding security.
Note:When upgrading from Oracle Database 10g Release 1 to 10g Release 2 or later,
CREATE EXTERNAL JOBis automatically granted to all users and roles that have the
CREATE JOBprivilege. Oracle recommends that you revoke this privilege from users that don't need it.