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

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还是更符合我们简单、快速的需求的。