oracle10g/11g 新特性五(用Shceduler自动化)

Simplifying Management Tasks

1.Performing a series of month-end tasks on the last day of each month

2.Runing a dequeue procedure as soon as a message is enqueued

3.Replicating table data via materialized view refreshes

4.Running a daily job to back up database

5.computing table and index statistics twice a day

6.Starting the batch load as soon as the file arrives on the file system

7.Generating an hourly report on invalid server access attempts

8.Rebuilding an index when finished rebuilding the current index

Core components

Your Basic Work Flow

1.Create a program (enabled or disabled) -optional

    a.To reuse this action within multiple jobs

    b.To change the schedule for a job without having to re-create the PL/SQL block

2.Create and use a schedule

3.Create and submit a job

Calendaring Expressions

实验

1.建表  usr1登录
SQL> create table log (user_name varchar2(10),user_date date);            
Table created.
2.建存储过程
SQL> create or replace procedure proc1 is                                 
  2  begin                                                                
  3  insert into log (user_name,user_date)                                
  4  values (user,sysdate);                                               
  5  commit;                                                              
  6  end;                                                                 
  7  /                                                                    
Procedure created.

3.以sys登录授权
SQL> grant create job to usr1;                                            
Grant succeeded.

4.建程序 
SQL> BEGIN                                                                
  2  DBMS_SCHEDULER.CREATE_PROGRAM(                                       
  3  program_name=>'prog1',                                               
  4  program_action=>'usr1.PROC1',                                        
  5  program_type=>'STORED_PROCEDURE',                                    
  6  enabled=>true);                                                      
  7  end;                                                                 
  8  /                                                                    
PL/SQL procedure successfully completed.


5.建SCHEDULE
SQL> begin                                                                
  2  dbms_scheduler.create_schedule(                                      
  3  schedule_name=>'schedule1',                                          
  4  start_date=> SYSTIMESTAMP,                                           
  5  end_date=>SYSTIMESTAMP+1,                                            
  6  repeat_interval=>'FREQ=MINUTELY;INTERVAL=1',                         
  7  comments=>'Every MINUTEL');                                          
  8  end;                                                                 
  9  /                                                                    

PL/SQL procedure successfully completed.

6.建JOB
SQL> begin                                                                                                                   
  2  dbms_scheduler.create_job(                                                                                              
  3  job_name=>'job1',                                                                                                       
  4  program_name=>'usr1.prog1',                                                                                             
  5  schedule_name=>'usr1.schedule1');                                                                                       
  6  end;                                                                                                                    
  7  /                                                                                                                       
PL/SQL procedure successfully completed.

7.监控
SQL> select job_name,log_date,status,error#,run_duration from user_scheduler_job_run_details order by log_date;
no rows selected


8.检查为什么
SQL> select job_name, enabled from user_scheduler_jobs;                   

JOB_NAME                       ENABL
------------------------------ -----
JOB1                           FALSE

9.enable job
 begin
  dbms_scheduler.enable('job1');
 end;
 /
 
 
SQL> select * from log;                                                   

USER_NAME  USER_DATE
---------- ---------
USR1       04-MAY-15


10.删除程序 
 BEGIN DBMS_SCHEDULER.DROP_PROGRAM( program_name=>'prog1');end;                                                                                                                             

11.删除SCHEDULE
SQL> begin dbms_scheduler.DROP_schedule(schedule_name=>'schedule1');end;                                                                

12.删除JOB
begin dbms_scheduler.drop_job(job_name=>'job2'); end;

持久性轻量作业11G

Creating a Time-Based Job

当job_type='PLSQL_BLOK' 表示能运行plsql块

Creating an Event-Based Schedule

Event-Based Scheduling

Creating Complex Schedules

Creating Job Chains

1.Create  chain--my_chain1
begin 
 dbms_scheduler.create_chain('my_chain1');
end;
select chain_name,enabled from user_scheduler_chains;

2.create chain step1-- my_prog1--my_proc1
  
  //创建存储过程
  create or replace procedure my_proc1
  is 
  begin
     null;
  end

 //创建  program
    begin
	dbms_scheduler.create_program(
	program_name=>'my_prog1',
	program_action=>'sys.my_prog1',
	program_type=>'STORED PROCEDURE',
	enabled=>TURE);
	END;
	/
//创建步骤
  begin
  dbms_scheduler.define_chain_step(
  chain_name=>'my_chain1',
  step_name=>'my_step1',
  program_name=>'my_prog1');
  end;

  
 3.create chain step2--prog1-proc1-log
  
   //创建存储过程
  create or replace procedure proc1
  is 
  begin
     null;
  end

 //创建  program
 
    begin
	dbms_scheduler.create_program(
	program_name=>'prog1',
	program_action=>'sys.proc1',
	program_type=>'STORED PROCEDURE',
	enabled=>TURE);
	END;
	/
  
    begin
  dbms_scheduler.define_chain_step(
  chain_name=>'my_chain1',
  step_name=>'my_step2',
  program_name=>'prog1');
  end;
  
 //查step
 select chain_name,step_name,program_name from user_scheduler_chain_steps;
 
 
 
4.定义规则
定义第一个规则
begin
dbms_scheduler.define_chain_rule(
chain_name=>'my_chain1',
condition=> 'true',
action=>'START my_step1',
rule_name=>'my_rule1',
comments=> 'start the chain');
end;

定义第二个规则
begin
dbms_scheduler.define_chain_rule(
chain_name=>'my_chain1',
condition=> 'my_step1 completed',
action=>'START my_step2',
rule_name=>'my_rule2',
comments=> 'start the chain');
end;

select chain_name,rule_name,condition,action from user_shceduler_rules

5.启用和检查
begin
dbms_scheduler.enable('my_chain1');
end;

select chain_name,enabled from user_scheduler_chains;

6. 手工开始执行  chain
Begin
 dbms_scheduler.run_chain(
 chain_name=>'my_chain1',
 start_steps=>'my_step1');
end;


7.自动执行  create job
begin
 DBMS_SCHEDULER.CREATE_JOB(
 job_name=>'chain_job_1',
 job_type=>'CHAIN',
 job_action=>'my_chain1',
 start_date=>SYSTIMESTAMP,
 end_date=>SYSTIMESTAMP+1,
 repeat_interval=>
 'FREQ=MINUTELY;INTERVAL=1',
 enabled=true);
 end;
 /

Advanced Scheduler Concepts

Window

Creating a Job Array

1.声明sys.job, sys.job_array变量

   DECLARE

   newjob sys.job;

   newjobarr sys.job_array;

2.Initialize the job array:

   BEGIN

   newjobarr:=sys.job_array()

3.Size the job array to hold the number of jobs needed:

    newjobarr.EXTEND(100);

4.Place jobs in the job array:

FOR i IN 1..100 LOOP

new job:=sys.job(job_name=>'LWTJK'||to_char(i),

                            job_style=>'LIGHTWEIGHT',

                            job_template=>'MY_PROG',

                           enabled=>TRUE);

newjobarr(i):=newjob

end loop;

5.Submit the Job array as one transaction

DBMS_SCHEDULER.CREATE_JOBS(newjobarr,'TRUNSACTIONAL');

Creating Remote Database Jobs