dbms_scheduler package系列(七)-1
本文繼續學習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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-754444/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dbms_scheduler package系列(七)-2Package
- dbms_scheduler package系列(六)Package
- dbms_scheduler package系列(五)Package
- dbms_scheduler package系列(四)Package
- dbms_scheduler package系列三Package
- dbms_scheduler package系列(二)Package
- dbms_scheduler package系列(一)Package
- dbms_scheduler package body INVALID 解決方案Package
- JAVA學習之路 （七） package包管理JavaPackage
- 深入理解javascript系列(七):閉包(1)JavaScript
- Go package(1) time 用法GoPackage
- dart系列之:建立Library packageDartPackage
- Oracle DBMS_SCHEDULEROracle
- HTB系列之七：BastardAST
- angularJS 系列（七）---指令AngularJS
- Go 執行 程式 test.go:1:1: expected ‘package‘, found ‘EOF‘GoPackage
- Java NIO系列教程（七） FileChannelJava
- not an rpm package (or package manifest):Package
- not an rpm package (or package manifest)Package
- packagePackage
- JAVA NIO 翻譯系列（七、FileChannel）Java
- 使用oracle dbms_scheduler代替crontabOracle
- dbms_scheduler 相關資料
- Package Specification 和 Package Body 及 Package有什麼區別？ (轉)Package
- 深入安卓Package Manager和Package Installer安卓Package
- AQS系列（七）- 終篇：AQS總結AQS
- JVM系列(七) – JVM線上監控工具JVM
- Pytorch系列:（七）模型初始化PyTorch模型
- JVM系列(七) - JVM線上監控工具JVM
- 分散式系列七: zookeeper簡單用法分散式
- jvm系列(七):jvm調優-工具篇JVM
- create table進階學習系列(七)
- 建立packagePackage
- Oracle定時任務dbms_schedulerOracle
- Package ssh is not available, but is referred to by another package 錯誤PackageAI
- Java NIO學習系列七：Path、Files、AsynchronousFileChannelJava
- Docker框架使用系列教程（七）Dockerfile映象建立Docker框架
- Java基礎系列（七）：物件與類（下）Java物件