Chapter 14 Using the Scheduler for Task Automation

Create a Job, Program, and Schedule
Understanding the Scheduler Architecture

DBA_SCHEDULER_JOBS view
CJQ0 monitor this view and lauches job slaves, the Jnnn processes, to run the jobs.
JOB_QUEUE_PROCESSES=0 Scheduler cannot run and will not function.
V$PROCESS
DBMS_SCHEDULER package

Jobs
what to do:
single SQL statement
a PL/SQL block
a PL/SQL, Java or external C stored procedure
any executable file: command, binary file, shell script or batch file

when to do:
timestamp at which to launch the job
repeat interval for future runs

DBMS_SCHEDULER.CREATE_JOB
JOB_NAME: unique within the schema

Program (PROGRAM_NAME)
JOB_TYPE: PLSQL_BLOCK, STORED_PROCEDURE, EXECUTABLE
JOB_ACTION: based on job_type
NUMBER_OF ARGUMENTS: arguments for job_action

Schedule (SCHEDULE_NAME)
START_DATE: first execution
REPEAT_INTERVAL: repeat frequency such as daily
END_DATE

JOB_CLASS: has to do with priorities and integration of the Scheduler with Resource Manager
ENABLED: default to FALSE
AUTO_DROP: default to TRUE
COMMENTS

Programs
DBMS_SCHEDULER.CREATE_PROGRAM
PROGRAM_NAME
PROGRAM_TYPE
PROGRAM_ACTION
NUMBER_OF_ARGUMENTS
ENABLED
COMMENTS

Schedules
DBMS_SCHEDULER.CREATE_SCHEDULE
SCHEDULE_NAME
START_DATE: default to the current date and time
REPEAT_INTERVAL: freq=, interval=, specifier=
END_DATE: without it, run forever
COMMENTS

Frequency:
YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, SECONDLY

Specifier:
BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYHOUR, BYMINUTE, BYSECOND

eg. repeat_interval=>'freq=weekly,; interval=2; byday=mon; byhour=6; byminutes=10'

Job Classes
to associate one or more jobs with a Resource Manager comsumer group, and also to control logging levels.
DBMS_SCHEDULER.CREATE_JOB_CLASS
JOB_CLASS_NAME
RESOURCE_CONSUMER_GROUP
SERVICE
LOGGING_LEVEL
LOG_HISTORY
COMMENTS

The RESOURCE_CONSUMER_GROUP nominates the group whose resource allocations should be applied to the running job, as determined by the Resource Manager plan in effect.

Windows
A window opens at a certain time and closes after a certain duration.
DBMS_SCHEDULER.CREATE_WINDOW
WINDOW_NAME
RESOURCE_PLAN
START_DATE
REPEAT_INTERVAL
END_DATE
DURATION INTERVAL DAY TO SECOND ' ::'
WINDOW_PRIORITY: LOW (default), HIGH
COMMENTS

Privileges
SCHEDULER_ADMIN includes the followings:

• CREATE JOB
• CREATE ANY JOB
• EXECUTE ANY PROGRAM
• EXECUTE ANY CLASS
• MANAGE SCHEDULER

It is granted to SYSTEM with ADMIN by default


Event-Based Jobs:-
CREATE_JOB
event_condition
queue_spec

DBMS_SCHEDULER.ENABLE('JOBNAME');
DBMS_SCHEDULER.RUN_JOB('JOBNAME');


Create Lightweight Jobs
ideal for running many short-duration jobs that run frequently.
using predefined Job Template, which can be a PROGRAM (plsql_block or stored_procedure).
inherit privileges from the template's program

CREATE_JOB
job_style: LIGHTWEIGHT, By default, it is REGULAR


Use Job Chains to Perform a Series of Related Task
Understanding Job Chains

1. CREATE_CHAIN (chain_name, comments)
2. DEFINE_CHAIN_STEP or DEFINE_CHAIN_EVENT_STEP (chain_name, step_name, program_name)
3. DEFINE_CHAIN_RULE (chain_name, rule_name, condition, action, comments)
4. ENABLE('chain_name')
5. CREATE_JOB with job_type of CHAIN

RUN_JOB or RUN_CHAIN(chain_name, job_name, start_steps)

Monitoring Job Chains using the following views:-
*_SCHEDULER_CHAINS
*_SCHEDULER_CHAIN_STEPS
*_SCHEDULER_CHAIN_RULES
*_SCHEDULER_RUNNING_CHAINS

Note that all jobs that do not specify a job class are assigned the job class DEFAULT_JOB_CLASS.

What happens if a window closes before a job that is run in the window has completed.
STOP_ON_WINDOW_CLOSE=FALSE (default)
The default behavior is that the job will continue until it finishes.

STOP_ON_WINDOW_CLOSE=TRUE
The job will abort if it has not finished by the time its window closes.

Use Advanced Scheduler Concepts to Prioritize Jobs
Level 1 Class Level: Resource Manager with different class of jobs based on the consumer group
Level 2 Job Level: Scheduler within the class based on job priority

So job with priority 1 might still run after job with priority 5 in another class if the second job's class is in a consumer group with a higher Resource Manager priority.

DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'job_name',
attribute=>'job_priority',
value=>2);

default is 3, range from 1 (highest) to 5.

DBMS_SCHEDULER.LOGGING_OFF
DBMS_SCHEDULER.LOGGING_RUNS
DBMS_SCHEDULER.LOGGING_FULL

DBA_SCHEDULER.JOB_LOG
DBA_SCHEDULER.JOB_RUN_DETAILS