Tips_36 Automating Administrative Tasks

■■CHAPTER 36　Automating Administrative Tasks

Earlier releases:DBMS_JOB
10g:Scheduler

■The Scheduler Architecture
・DBA_SCHEDULER_JOBS:a storage point for all Scheduler jobs
・CJQ0 process:monitors this table and when necessary launches job slaves
・Jnnn processe:to run the jobs.
*the maximum number of Jnnn processes is limited by the JOB_QUEUE_PROCESSES instance parameter;this defaults to 0, must be greater than zero or the Scheduler cannot run.
･V$PROCESS:the processes currently running
The Scheduler can be configured and monitored with an API.the DBMS_SCHEDULER package.or through Database Control

■Scheduler Objects
･Jobs：A job specifies what to do, and when to do it.
 DBMS_SCHEDULE.CREATE_JOB
･Programs：Programs provide a layer of abstraction between the job and the action it will perform.
 DBMS_SCHEDULER.CREATE_PROGRAM
･Schedules：A schedule is a specification for when and how frequently a job should run.
 DBMS_SCHEDULER.CREATE_SCHEDULE
*One schedule can be applied to many jobs; one program can be invoked by many jobs.
･Job Classes：A job class is used to associate one or more jobs with a Resource Manager consumer group, and also to control logging levels.
 DBMS_SCHEDULER.CREATE_JOB_CLASS
･Windows:A schedule specifies exactly when a job should be launched. Windows extend the concept of schedules, by giving Oracle more freedom to decide when to run the job
 DBMS_SCHEDULER.CREATE_WINDOW
*Windows share the same namespace as schedules
*Programs share the same namespace as jobs
■Privileges
. CREATE JOB
. CREATE ANY JOB
. EXECUTE ANY PROGRAM
. EXECUTE ANY CLASS
. MANAGE SCHEDULER
. EXECUTE ON
. ALTER ON
. ALL ON

■■Creating and Scheduling Jobs
■Exercise:Creating a Job with the Scheduler API:
1. Connect to your database as user SYSTEM using SQL*Plus.
2. Create a table to store times, and set your date format to show the date and time.
SQL> create table times (c1 date);
SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
3. Create a job to insert the current time into the table every minute.
SQL> begin
2 dbms_scheduler.create_job(
3 job_name=>'savedate',
4 job_type=>'plsql_block',
5 job_action=>'insert into times values(sysdate);',
6 start_date=>sysdate,
7 repeat_interval=>'freq=minutely;interval=1',
8 enabled=>true,
9 auto_drop=>false);
10 end;
11 /
PL/SQL procedure successfully completed.
4. Query the job table to see that the job is scheduled.
SQL> select job_name,enabled,to_char(next_run_date,'dd-mm-yy
hh24:mi:ss'),run_count from user_scheduler_jobs;
JOB_NAME ENABL TO_CHAR(NEXT_RUN_ RUN_COUNT
------------------------- ----- ----------------- ----------
SAVEDATE TRUE 15-01-05 14:58:03 2
5. Query the times table to demonstrate that the inserts are occurring.
SQL> select * from times;
6. Disable the job.
SQL> exec dbms_scheduler.disable('savedate');
7. Re-run the queries from Steps 4 and 5

■■Using Programs and Schedules
Programs and schedules let you reuse Scheduler components for similar tasks.
If you create a program with the CREATE_PROGRAM procedure, then (just as with jobs) the program will be disabled by default. Change this default either by specifying the ENABLED argument as TRUE when you create the program or by using the ENABLE procedure subsequently:
SQL> exec dbms_scheduler.enable('program1');

■■Using Classes, Windows, and the Resource Manager