本文继续学习dbms_scheduler包的其它用法,是为dbms_scheduler package系列七
创建job几种方式:
Creating Jobs Using a Named Program
Creating Jobs Using a Named Schedule
Creating Jobs Using a Named Program and Schedule
用指定的program创建job
Creating Jobs Using a Named Program
1,不要指定job_type,job_action,number_of_arguments参数值
---创建program
SQL> begin
2 dbms_scheduler.create_program(program_name => 'program_1',program_type => 'stored_procedure',program_action => 'proc_arg',enabled => true,comments => 'program 1 comments');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> begin
2 dbms_scheduler.create_job(job_name => 'job_program',job_type => 'stored_procedure',job_action => 'proc_arg',program_name=>'program_1');
3 end;
4 /
begin
dbms_scheduler.create_job(job_name => 'job_program',job_type => 'stored_procedure',job_action => 'proc_arg',program_name=>'program_1');
end;
ORA-06550: line 3, column 1:
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
--移除jog type及job action参数后正确运行
SQL> begin
2 dbms_scheduler.create_job(job_name => 'job_program',program_name=>'program_1');
3 end;
4 /
PL/SQL procedure successfully completed
用指定的schedule创建job
Creating Jobs Using a Named Schedule
不要指定start_date,repeat_interval,end_date参数值
---引用之前创建的schedule,注意:已含有start_date,repeat_interval,end_date参数值,故不用在create_job再次指定其相应的值
SQL> select * from user_scheduler_schedules;
SCHEDULE_NAME SCHEDULE_TYPE START_DATE REPEAT_INTERVAL EVENT_QUEUE_OWNER EVENT_QUEUE_NAME EVENT_QUEUE_AGENT EVENT_CONDITION FILE_WATCHER_OWNER FILE_WATCHER_NAME END_DATE COMMENTS
------------------------------ ------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
MY_SCHEDULE CALENDAR 31-JAN-13 11.03.49.798000 AM +08:00 FREQ=DAILY;BYHOUR=12,13 my_schedule_comment
---为start_date指定参数值报错
SQL> begin
2 dbms_scheduler.create_job(job_name=>'job_schedule',job_action=>'proc_arg',job_type=>'stored_procedure',schedule_name=>'my_schedule',start_date => sysdate);
3 end;
4 /
begin
dbms_scheduler.create_job(job_name=>'job_schedule',job_action=>'proc_arg',job_type=>'stored_procedure',schedule_name=>'my_schedule',start_date => sysdate);
end;
ORA-06550: line 3, column 1:
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
----修正移除start_date正确
SQL> begin
2 dbms_scheduler.create_job(job_name=>'job_schedule',job_action=>'proc_arg',job_type=>'stored_procedure',schedule_name=>'my_schedule');
3 end;
4 /
PL/SQL procedure successfully completed
使用指定的program和schedule创建job
Creating Jobs Using a Named Program and Schedule
SQL> select * from user_scheduler_programs;
PROGRAM_NAME PROGRAM_TYPE PROGRAM_ACTION NUMBER_OF_ARGUMENTS ENABLED DETACHED SCHEDULE_LIMIT PRIORITY WEIGHT MAX_RUNS MAX_FAILURES MAX_RUN_DURATION NLS_ENV COMMENTS
------------------------------ ---------------- -------------------------------------------------------------------------------- ------------------- ------- -------- ------------------------------------------------------------------------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
PROGRAM_1 STORED_PROCEDURE proc_arg 0 TRUE FALSE 3 1 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC program 1 comments
SQL> select * from user_scheduler_schedules;
SCHEDULE_NAME SCHEDULE_TYPE START_DATE REPEAT_INTERVAL EVENT_QUEUE_OWNER EVENT_QUEUE_NAME EVENT_QUEUE_AGENT EVENT_CONDITION FILE_WATCHER_OWNER FILE_WATCHER_NAME END_DATE COMMENTS
------------------------------ ------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
MY_SCHEDULE CALENDAR 31-JAN-13 11.03.49.798000 AM +08:00 FREQ=DAILY;BYHOUR=12,13 my_schedule_comment
SQL> begin
2 dbms_scheduler.create_job(job_name=>'job_program_schedule',program_name=>'program_1',schedule_name=>'my_schedule');
3 end;
4 /
PL/SQL procedure successfully completed
复制job
Copying Jobs
唯一与源job区别是,有新的job name且状态为disable
---复制job自job_program_schedule
SQL> begin
2 dbms_scheduler.copy_job(old_job => 'job_program_schedule',new_job => 'copy_job');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> select * from user_scheduler_programs;
PROGRAM_NAME PROGRAM_TYPE PROGRAM_ACTION NUMBER_OF_ARGUMENTS ENABLED DETACHED SCHEDULE_LIMIT PRIORITY WEIGHT MAX_RUNS MAX_FAILURES MAX_RUN_DURATION NLS_ENV COMMENTS
------------------------------ ---------------- -------------------------------------------------------------------------------- ------------------- ------- -------- ------------------------------------------------------------------------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
PROGRAM_1 STORED_PROCEDURE proc_arg 0 TRUE FALSE 3 1 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC program 1 comments
---查看复制后的job信息
SQL> select * from user_scheduler_jobs;
JOB_NAME JOB_SUBNAME JOB_STYLE JOB_CREATOR CLIENT_ID GLOBAL_UID PROGRAM_OWNER PROGRAM_NAME JOB_TYPE JOB_ACTION NUMBER_OF_ARGUMENTS SCHEDULE_OWNER SCHEDULE_NAME SCHEDULE_TYPE START_DATE REPEAT_INTERVAL EVENT_QUEUE_OWNER EVENT_QUEUE_NAME EVENT_QUEUE_AGENT EVENT_CONDITION EVENT_RULE FILE_WATCHER_OWNER FILE_WATCHER_NAME END_DATE JOB_CLASS ENABLED AUTO_DROP RESTARTABLE STATE JOB_PRIORITY RUN_COUNT MAX_RUNS FAILURE_COUNT MAX_FAILURES RETRY_COUNT LAST_START_DATE LAST_RUN_DURATION NEXT_RUN_DATE SCHEDULE_LIMIT MAX_RUN_DURATION LOGGING_LEVEL STOP_ON_WINDOW_CLOSE INSTANCE_STICKINESS RAISE_EVENTS SYSTEM JOB_WEIGHT NLS_ENV SOURCE NUMBER_OF_DESTINATIONS DESTINATION_OWNER DESTINATION CREDENTIAL_OWNER CREDENTIAL_NAME INSTANCE_ID DEFERRED_DROP ALLOW_RUNS_IN_RESTRICTED_MODE COMMENTS FLAGS
------------------------------ ------------------------------ ----------- ------------------------------ ---------------------------------------------------------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- -------------------------------------------------------------------------------- ------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------- --------- ----------- --------------- ------------ ---------- ---------- ------------- ------------ ----------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------- -------------------- ------------------- -------------------------------------------------------------------------------- ------ ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ----------- ------------- ----------------------------- -------------------------------------------------------------------------------- ----------
JOB_PROGRAM_SCHEDULE REGULAR SCOTT SCOTT PROGRAM_1 SCOTT MY_SCHEDULE NAMED DEFAULT_JOB_CLASS FALSE TRUE FALSE DISABLED 3 0 0 0 OFF FALSE TRUE FALSE 1 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC 1 FALSE FALSE 4195376
COPY_JOB REGULAR SCOTT SCOTT PROGRAM_1 SCOTT MY_SCHEDULE NAMED DEFAULT_JOB_CLASS FALSE TRUE FALSE DISABLED 3 0 0 0 OFF FALSE TRUE FALSE 1 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC 1 FALSE FALSE 4195376