Scheduler Reference

This section contains reference information for Oracle Scheduler. It contains the following topics:

Scheduler Privileges

Table 28-2 lists the various Scheduler privileges.

Table 28-2 Scheduler Privileges

Privilege Name Operations Authorized

System Privileges:

 

CREATE JOB

This privilege enables you to create jobs, chains, schedules, programs, and credentials in your own schema. You will always be able to alter and drop jobs, schedules and programs in your own schema, even if you do not have the CREATE JOB privilege. In this case, the job must have been created in your schema by another user with the CREATE ANY JOB privilege.

CREATE ANY JOB

This privilege enables you to create, alter, and drop jobs, chains, schedules, programs, and credentials in any schema except SYS. This privilege is extremely powerful and should be used with care because it allows the grantee to execute any PL/SQL code as any other database user.

CREATE EXTERNAL JOB

This privilege is required to create jobs that run outside of the database. Owners of jobs of type 'EXECUTABLE' or jobs that point to programs of type 'EXECUTABLE' require this privilege. To run a job of type 'EXECUTABLE', you must have this privilege and the CREATE JOB privilege. This privilege is also required to retrieve files from a remote host and to save files to one or more remote hosts.

EXECUTE ANY PROGRAM

This privilege enables your jobs to use programs or chains from any schema.

EXECUTE ANY CLASS

This privilege enables your jobs to run under any job class.

MANAGE SCHEDULER

This is the most important privilege for administering the Scheduler. It enables you to create, alter, and drop job classes, windows, and window groups. It also enables you to set and retrieve Scheduler attributes, purge Scheduler logs, drop public credentials, set the agent password for a database.

Object Privileges:

 

EXECUTE

This privilege can only be granted for programs, chains, and job classes. It enables you to create a job that runs with the program, chain, or job class. It also enables you to view object attributes.

ALTER

This privilege enables you to alter or drop the object it is granted on. Altering includes such operations as enabling, disabling, defining or dropping program arguments, setting or resetting job argument values and running a job. For programs, jobs, and chains, this privilege enables you to view object attributes. This privilege can only be granted on jobs, chains, programs and schedules. For other types of Scheduler objects, you can grant the MANAGE SCHEDULER system privilege. This privilege can be granted for:

jobs (DROP_JOB, RUN_JOB, ALTER_RUNNING_CHAIN, SET_JOB_ARGUMENT_VALUE, RESET_JOB_ARGUMENT_VALUE, SET_JOB_ANYDATA_VALUE) and (STOP_JOB without the force option)

chains (DROP_CHAIN, ALTER_CHAIN, DEFINE_CHAIN_RULE, DEFINE_CHAIN_STEP, DEFINE_CHAIN_EVENT_STEP, DROP_CHAIN_RULE, and DROP_CHAIN_STEP)

programs (DROP_PROGRAM, DEFINE_PROGRAM_ARGUMENT, DEFINE_ANYDATA_ARGUMENT, DEFINE_METADATA_ARGUMENT, DROP_PROGRAM_ARGUMENT, SET_ATTRIBUTE_NULL)

schedules (DROP_SCHEDULE)

ALL

This privilege authorizes operations allowed by all other object privileges possible for a given object. It can be granted on jobs, programs, chains, schedules and job classes.


The SCHEDULER_ADMIN role is created with all of the system privileges shown in Table 28-2 (with the ADMIN option). The SCHEDULER_ADMIN role is granted to DBA (with the ADMIN option).

The following object privileges are granted to PUBLIC: SELECT ALL_SCHEDULER_* views, SELECT USER_SCHEDULER_* views, SELECT SYS.SCHEDULER$_JOBSUFFIX_S (for generating a job name), and EXECUTE SYS.DEFAULT_JOB_CLASS.

Scheduler Data Dictionary Views

You can check Scheduler information by using many views. An example is the following, which shows information for completed instances of my_job1:

SELECT JOB_NAME, STATUS, ERROR#
FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'MY_JOB1';

JOB_NAME     STATUS           ERROR#
--------     --------------   ------
MY_JOB1      FAILURE           20000

Table 28-3 contains views associated with the Scheduler. The *_SCHEDULER_JOBS, *_SCHEDULER_SCHEDULES, *_SCHEDULER_PROGRAMS, *_SCHEDULER_RUNNING_JOBS, *_SCHEDULER_JOB_LOG, *_SCHEDULER_JOB_RUN_DETAILS views are particularly useful for managing jobs. See Oracle Database Reference for details regarding Scheduler views.

Note:

In the following table, the asterisk at the beginning of a view name can be replaced with DBA, ALL, or USER.

Table 28-3 Scheduler Views

View Description
*_SCHEDULER_CHAIN_RULES

These views show all rules for all chains.

*_SCHEDULER_CHAIN_STEPS

These views show all steps for all chains.

*_SCHEDULER_CHAINS

These views show all chains.

*_SCHEDULER_CREDENTIALS

These views show all credentials.

*_SCHEDULER_GLOBAL_ATTRIBUTE

These views show the current values of Scheduler attributes.

*_SCHEDULER_JOB_ARGS

These views show all set argument values for all jobs.

*_SCHEDULER_JOB_CLASSES

These views show all job classes.

*_SCHEDULER_JOB_LOG

These views show job runs and state changes, depending on the logging level set.

*_SCHEDULER_JOB_ROLES

These views show all jobs by Oracle Data Guard database role.

*_SCHEDULER_JOB_RUN_DETAILS

These views show all completed (failed or successful) job runs.

*_SCHEDULER_JOBS

These views show all jobs, enabled as well as disabled.

*_SCHEDULER_PROGRAM_ARGS

These views show all arguments defined for all programs as well as the default values if they exist.

*_SCHEDULER_PROGRAMS

These views show all programs.

*_SCHEDULER_RUNNING_CHAINS

These views show all chains that are running.

*_SCHEDULER_RUNNING_JOBS

These views show state information on all jobs that are currently being run.

*_SCHEDULER_SCHEDULES

These views show all schedules.

*_SCHEDULER_WINDOW_DETAILS

These views show all completed window runs.

*_SCHEDULER_WINDOW_GROUPS

These views show all window groups.

*_SCHEDULER_WINDOW_LOG

These views show all state changes made to windows.

*_SCHEDULER_WINDOWS

These views show all windows.

*_SCHEDULER_WINGROUP_MEMBERS

These views show the members of all window groups, one row for each group member.