ITPub博客

首页 > Linux操作系统 > Linux操作系统 > dbms_scheduler

dbms_scheduler

原创 Linux操作系统 作者:to_be_dba 时间:2013-07-03 00:09:13 0 删除 编辑

11g中推荐用dbms_scheduler代替dbms_job。今天研究一下dbms_scheduler。

该包有以下规则(使用该包时):
1)只有sys用户可以在sys模式下做操作;
2)一些存储过程可以接受以逗号分隔的对象列表。比如dbms_scheduler.stop_job('job1,job2,job3')。
若job2未正确关闭,job3就不会被执行关闭操作。
3)在不存在的对象上执行操作,将导致pl/sql的报错。

该包的内容很多:
SQL> select distinct text from dba_source where name='DBMS_SCHEDULER' and text like 'PROCEDURE %' order by text;

TEXT
--------------------------------------------------------------------------------
PROCEDURE  create_group(
PROCEDURE add_event_queue_subscriber(
PROCEDURE add_group_member (
PROCEDURE add_job_email_notification
PROCEDURE add_window_group_member(
PROCEDURE alter_chain(
PROCEDURE alter_running_chain(
PROCEDURE analyze_chain(
PROCEDURE auto_purge;
PROCEDURE close_window(
PROCEDURE copy_job(
PROCEDURE create_chain(
PROCEDURE create_credential(
PROCEDURE create_database_destination(
PROCEDURE create_event_schedule(
PROCEDURE create_job(
PROCEDURE create_job_class(
PROCEDURE create_jobs(
PROCEDURE create_program(
PROCEDURE create_schedule(

TEXT
--------------------------------------------------------------------------------
PROCEDURE create_window(
PROCEDURE create_window_group(
PROCEDURE define_anydata_argument(
PROCEDURE define_chain_event_step(
PROCEDURE define_chain_rule(
PROCEDURE define_chain_step(
PROCEDURE define_metadata_argument(
PROCEDURE define_program_argument(
PROCEDURE disable(
PROCEDURE disable1_calendar_check;
PROCEDURE drop_agent_destination(
PROCEDURE drop_chain(
PROCEDURE drop_chain_rule(
PROCEDURE drop_chain_step(
PROCEDURE drop_credential(
PROCEDURE drop_database_destination(
PROCEDURE drop_group(
PROCEDURE drop_job(
PROCEDURE drop_job_class(
PROCEDURE drop_program(
PROCEDURE drop_program_argument (

TEXT
--------------------------------------------------------------------------------
PROCEDURE drop_schedule(
PROCEDURE drop_window(
PROCEDURE drop_window_group(
PROCEDURE enable(
PROCEDURE end_detached_job_run (
PROCEDURE evaluate_running_chain(
PROCEDURE get_attribute(
PROCEDURE get_scheduler_attribute(
PROCEDURE open_window(
PROCEDURE purge_log(
PROCEDURE remove_event_queue_subscriber(
PROCEDURE remove_group_member(
PROCEDURE remove_job_email_notification
PROCEDURE remove_window_group_member(
PROCEDURE reset_job_argument_value(
PROCEDURE run_chain(
PROCEDURE run_job(
PROCEDURE set_agent_registration_pass(
PROCEDURE set_attribute(
PROCEDURE set_attribute_null(
PROCEDURE set_job_anydata_value(

TEXT
--------------------------------------------------------------------------------
PROCEDURE set_job_argument_value(
PROCEDURE set_job_attributes(
PROCEDURE set_scheduler_attribute(
PROCEDURE show_errors(
PROCEDURE stop_job(
PROCEDURE submit_remote_external_job (

68 rows selected

 

该包是重载的(有多种调用方式)。

DBMS_SCHEDULER.CREATE_JOB (
job_name IN VARCHAR2,--job名称,必须输入,可以指定GENERATE_JOB_NAME存储过程自动生成名称
job_type IN VARCHAR2,--plsql_block:表示job是pl/sql块,argument数量只能是0
                     --stored_procedure:表示job是外部的pl/sql存储过程,也可以是c或java程序
                     --executable:表示job在数据库之外,也就是可以用操作系统命令执行的命令
job_action IN VARCHAR2,
number_of_arguments IN PLS_INTEGER DEFAULT 0,--job中允许发生的argument的数量
start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,--起始时间,为空表示开启后立即执行 程序并不能保证job执行间隔的准确性,因为job本身的执行也需要时间
repeat_interval IN VARCHAR2 DEFAULT NULL,--重复执行的次数,若为空 则只执行一次
end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,--结束时间。此时间以后job变为completed状态,enabled状态为false
job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',--job属于的类型。job的拥有者必须拥有对其所属job类型的执行权限
enabled IN BOOLEAN DEFAULT FALSE,--创建后是否启用,默认为不启用,需要手工修改状态
auto_drop IN BOOLEAN DEFAULT TRUE,--job执行完成(状态变为completed)后是否自动删除job;若设置为false,仍可以在*_scheduler_jobs视图中看到。默认为true
comments IN VARCHAR2 DEFAULT NULL);

1)
DBMS_SCHEDULER.CREATE_JOB (
job_name IN VARCHAR2,
program_name IN VARCHAR2,--与此job相关的程序名
schedule_name IN VARCHAR2,--job相关的日程表、窗口、窗口组的名称
job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
enabled IN BOOLEAN DEFAULT FALSE,
auto_drop IN BOOLEAN DEFAULT TRUE,
comments IN VARCHAR2 DEFAULT NULL);

2)
DBMS_SCHEDULER.CREATE_JOB (
job_name IN VARCHAR2,
program_name IN VARCHAR2,
start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
repeat_interval IN VARCHAR2 DEFAULT NULL,
end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
enabled IN BOOLEAN DEFAULT FALSE,
auto_drop IN BOOLEAN DEFAULT TRUE,
comments IN VARCHAR2 DEFAULT NULL);

3)
Creates a job using a named schedule object and an inlined program or program:
DBMS_SCHEDULER.CREATE_JOB (
job_name IN VARCHAR2,
schedule_name IN VARCHAR2,
job_type IN VARCHAR2,
job_action IN VARCHAR2,
number_of_arguments IN PLS_INTEGER DEFAULT 0,
job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
enabled IN BOOLEAN DEFAULT FALSE,
auto_drop IN BOOLEAN DEFAULT TRUE,
comments IN VARCHAR2 DEFAULT NULL);

以下我们说明第一种情况的步骤。

首先创建一个日程表(schedule):
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name IN VARCHAR2,
start_date IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
repeat_interval IN VARCHAR2,
end_date IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
comments IN VARCHAR2 DEFAULT NULL);

再创建一个程序(program):
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name IN VARCHAR2,
program_type IN VARCHAR2,
program_action IN VARCHAR2,
number_of_arguments IN PLS_INTEGER DEFAULT 0,
enabled IN BOOLEAN DEFAULT FALSE,
comments IN VARCHAR2 DEFAULT NULL);

[oracle@localhost ~]$ sqlplus /nolog
conn /as
SQL*Plus: Release 11.2.0.1.0 Production on Sat May 25 15:33:02 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 SQL> sysdba
Connected.
SQL> conn scott/scott
Connected.

SQL> declare
  2  begin
  3    dbms_scheduler.create_schedule(
  4    schedule_name => 'EXECUTE_EVERT_MIN',
  5    start_date => sysdate,
  6    repeat_interval =>'FREQ=MINUTELY;BYMINUTE=1',--表示每分钟执行一次,freq也可以是"YEARLY" | "MONTHLY" | "WEEKLY"| "DAILY" |"HOURLY" | "MINUTELY" | "SECONDLY"
  7    end_date => sysdate+1,
  8    comments => 'execute every minutes'
  9    );
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> begin
  2    DBMS_SCHEDULER.CREATE_PROGRAM (
  3    program_name=>'SCOTT_P_TEST',
  4    program_type=>'STORED_PROCEDURE',
  5    program_action=>'P_TEST',
  6    number_of_arguments=>0,
  7    enabled=>true,
  8    comments=>NULL);
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> begin
  2    DBMS_SCHEDULER.CREATE_JOB (
  3    job_name=>'TEST_JOB',
  4    program_name=>'SCOTT_P_TEST',
  5    schedule_name=>'EXECUTE_EVERT_MIN',
  6  --  job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
  7    enabled=>true,
  8    auto_drop=>true,
  9    comments=>'a test job create on 20130702');
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.enable('TEST_JOB');

PL/SQL procedure successfully completed.

需要注意,以上步骤中不管是存储过程、系统默认的参数,在调用时都是用单引号包含的。否则会报错。

按照我的想法,这时候应该就能够正常执行了。
但查看t_2表,没有任何记录。
而且查看user_scheduler_jobs,看到:

SQL> select t.enabled,t.auto_drop,t.job_name,t.schedule_owner,
  2  t.schedule_name,t.job_class,t.state,t.run_count
  3  from user_scheduler_jobs t;

ENABLED AUTO_DROP JOB_NAME  SCHEDULE_OWNER     SCHEDULE_NAME         JOB_CLASS            STATE       RUN_COUNT
------- --------- --------- ------------------ --------------------- -------------------- ---------- ----------
TRUE    TRUE      TEST_JOB  SCOTT              EXECUTE_EVERT_MIN     DEFAULT_JOB_CLASS    SCHEDULED           0

为什么没执行呢?
以下一个语句的结果需要注意,并没有像正常的语句那样提示执行成功。
这是因为权限不足。
SQL> exec dbms_scheduler.run_job('TEST_JOB',use_current_session => true);      
                                                                               
SQL> select t.enabled,t.auto_drop,t.job_name,t.schedule_owner,                 
  2  t.schedule_name,t.job_class,t.state,t.run_count                           
  3  from user_scheduler_jobs t;                                               
                                                                               
ENABL AUTO_ JOB_NAME                                                           
----- ----- ------------------------------                                     
SCHEDULE_OWNER                                                                 
--------------------------------------------------------------------------------
SCHEDULE_NAME                                                                  
--------------------------------------------------------------------------------
JOB_CLASS                      STATE            RUN_COUNT                      
------------------------------ --------------- ----------                      
TRUE  TRUE  TEST_JOB                                                           
SCOTT                                                                          
EXECUTE_EVERT_MIN                                                              
DEFAULT_JOB_CLASS              SCHEDULED                0                      
                                                                               
登录到sys用户,为scott赋予权限:                                                                               
SQL> conn /as sysdba                                                           
Connected.                                                                     
SQL> grant execute on DEFAULT_JOB_CLASS  to scott                              
  2  ;                                                                         
                                                                               
Grant succeeded.                                                               
                                                                               
SQL> conn scott/scott                                                          
Connected.                                                                     
SQL> exec dbms_scheduler.run_job('TEST_JOB',use_current_session => true);      
                                                                               
PL/SQL procedure successfully completed.                                       
                                                                               
SQL> select t.enabled,t.auto_drop,t.job_name,t.schedule_owner,                 
  2  t.schedule_name,t.job_class,t.state,t.run_count                           
  3  from user_scheduler_jobs t;                                               
                                                                               
ENABL AUTO_ JOB_NAME                                                           
----- ----- ------------------------------                                     
SCHEDULE_OWNER                                                                 
--------------------------------------------------------------------------------
SCHEDULE_NAME                                                                  
--------------------------------------------------------------------------------
JOB_CLASS                      STATE            RUN_COUNT                      
------------------------------ --------------- ----------                      
TRUE  TRUE  TEST_JOB                                                           
SCOTT                                                                          
EXECUTE_EVERT_MIN                                                              
DEFAULT_JOB_CLASS              SCHEDULED                1                      
                                                                               
                                                                               
SQL> select * from t_2;                                                        
                                                                               
TIME                                                                           
---------                                                                      
CONTENT                                                                        
--------------------------------------------------------------------------------
25-MAY-13                                                                      
: there are 17 records on table t.                                             

虽然这次job执行了,但并不能按照我的想法每分钟执行。看来理解上还有偏差。

dbms_scheduler包比dbms_job细化很多,结构有点复杂。但考虑到了job的停止,具体时间的设定,并对日程表、job名称、要完成的程序等都进行了命名。
虽然oracle推荐使用dbms_scheduler代替dbms_jobs,但从使用的角度来说dbms_jobs还是更符合我们简单、快速的需求的。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-765354/,如需转载,请注明出处,否则将追究法律责任。

上一篇: mysql初识(二)
请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    148
  • 访问量
    392695