Chapter 36 – Automating Administrative Tasks

The Scheduler Architecture

• The DBA_SCHEDULER_JOBS view in the data dictionary holds info for all scheduled jobs.
• The background process CJQ0 monitors the table and launches other processes – Jnnn – to run the jobs.
• The Jnnn process relies on the parameter JOB_QUEUE_PROCESSES, which defaults to 0. If it is set to 0, then jobs won’t run.

Scheduler Objects

Jobs

• A job specifies what to do and when to do it.
• The what can be:
  • Single SQL statement
  • PL/SQL Block
  • PL/SQL Stored Procedure
  • Java stored procedure
  • An external procedure
  • An executable or script on the OS
• The when can be:
  • A pre-defined schedule
  • A pre-defined window
  • An event
  • A time
• Jobs can be created via the dbms_scheduler.create_job procedure
  • All forms of the create_job procedure require job_name
  • Other options:
    • job_type
    • plsql_block
    • stored_procedure
    • executable
    • job_priority (defaults to 3 but can be 1 – 5, with 1 being highest priority)
    • job_action
    • auto_drop (says to execute the job only once – it’s the default if no scheduling info exists) …

Programs

• Programs are like pulling the what out of a job
• Created with the dbms_scheduler.create_program procedure
• Arguments
  • program_name
  • program_type
  • program_action
  • number_of_arguments
  • enabled
  • comments

Schedules

• Schedules are like pulling the when out of a job
• Created with the dbms_scheduler.create_schedule procedure
• Arguments
  • schedule_name
  • start_date
  • repeat_interval
  • end_date
  • comments
• Repeat Interval examples:
  • repeat_interval => ‘freq=hourly; interval=12′
  • repeat_interval => ‘freq=yearly; bymonth=jan,apr,jul,oct; bymonthday=2′
  • repeat_interval => ‘freq=weekly; interval=2; byday=mon; byhour=3; byminute=12′

Job Classes

• A job class is used to associate jobs with consumer groups. This will allow jobs to be run with specified resources, etc. It also controls logging.
• Arguments
  • job_class_name
  • resource_consumer_group
  • service (RAC only – service name of instance to run on)
  • logging_level
  • log_history
  • comments
• Created with dbms_scheduler.create_job_class

Windows

• A window adds functionality to the schedule by giving Oracle a window of time in which to run a job. Jobs scheduled within a window can be run at any time which is chosen by Oracle’s discretion.
• It is possible to nominate a RESOURCE_PLAN to start when the window begins as well as a WINDOW_PRIORITY (HIGH | LOW) to manage overlapping windows.
• It is possible to combine windows into window groups for ease of administration.
• Defaults are WEEKEND_WINDOW & WEEKNIGHT WINDOW.
• Windows cannot have the same name as schedules because they share the same namespace.
• Arguments
  • window_name
  • resource_plan
  • start_date
  • repeat_interval
  • end_date
  • duration
  • window_priority (either high or low)
    • if two windows have the same priority, then the one opened first will have priority
  • comments
• The duration is of the form:
  • ‘days hours:minutes:seconds’ // ‘0 3:30:00′

Privileges

• Example
  • GRANT CREATE ANY JOB TO HR;
• List of privs:
  • CREATE JOB (a user can create his own jobs)
  • CREATE ANY JOB (a user can create jobs in other schemas)
  • EXECUTE ANY PROGRAM
  • EXECUTE ANY CLASS
  • MANAGE SCHEDULER
  • EXECUTE ON <job, program or class>
  • ALTER ON <job, program or schedule>
  • ALL ON <job, program, schedule or class>

Using Programs and Schedules

• After creating a program or job via create_program or create_job, you will also need to enable it (since it is disabled by default)
  • SQL> exec dbms_scheduler.enable(‘prog1′);
• Programs cannot have the same names as jobs because they share the same namespace.
• A job can be executed without a schedule manually:
  • SQL> exec dbms_scheduler.run_job(’savedate’);

Using Classes, Windows and the Resource Manager with Jobs

• Jobs can be configured with the following components:
  • Job classes – jobs are assigned a class (logging)
  • Consumer groups – classes are assigned to consumer groups (CPU, active session pool)
  • Resource plans – consumer groups are part of a resource plan (only one resource plan active at a time)
  • Windows – resource plans and jobs will be active during windows
  • Window groups – windows can be combined into groups for administration purposes

Preconfigured Jobs

• There are two jobs configured by default:
  • PURGE_LOG
  • GATHER_STATS_JOB

Packages

• DBMS_SCHEDULE.
  • CREATE_JOB
  • CREATE_PROGRAM
  • CREATE_SCHEDULE
  • CREATE_JOB_CLASS
  • CREATE_WINDOW

Processes

• CJQ0 – launches Jnnn processes when they are scheduled to run
• Jnnn – each process runs a job (ex. J0001, J0002, …)

Tables & Views

• DBA_SCHEDULER_JOBS – shows scheduled jobs info

Parameters

• JOB_QUEUE_PROCESSES – defaults to 0 which means that jobs won’t run.
• RESOURCE_MANAGER_PLAN – current plan for the instance