dbms_scheduler package系列(四)
dbms_scheduler package系列
前面幾文講述了dbms_scheduler包的相關概念及內在聯絡;本文開始講解如何
使用這個包.內容主要有如下幾方面:
(注:如下知識皆與上幾文中所述概念關聯匹配起來,可見概念的重要性,概念為技術之本.
scheduler Objects and Their Naming
Using Jobs
?
Using Programs
?
Using Schedules
?
Using Job Classes
?
Using Windows
?
Using Window Groups
?
Using Events
?
Using Chains
?
Allocating Resources Among Jobs
排程器物件及其命名規則
scheduler Objects and Their Naming
1,和其它的資料物件命名規則一樣
2,如建立一個job名為my_job,等同於MY_JOB或者My_Job,但卻和加上雙引號的"my_job"是兩個物件
即不未雙引號的命名是不區分大小的,而加上雙引號的物件是區分大小寫的
輯錄測試:
SQL> create table t_comm(a int);
Table created
SQL> create table "t_comm"(a int);
Table created
SQL> desc t_comm;
Name Type Nullable Default Comments
---- ------- -------- ------- --------
A INTEGER Y
SQL> desc "t_comm";
Name Type Nullable Default Comments
---- ------- -------- ------- --------
A INTEGER Y
SQL> create table "t_COMM"(a int);
Table created
使用job例項
using jobs
一個job是什麼呢,它是一個scheduler和program的組合體,以及執行program所需要的引數;
A job is the combination of a schedule and a program, along with any additional arguments required by the program. This section introduces you to basic job tasks,
使用 job主要討論以下幾個主題:
and discusses the following topics:
?
Job Tasks and Their Procedures
?
Creating Jobs
?
Copying Jobs
?
Altering Jobs
?
Running Jobs
?
Stopping Jobs
?
Dropping Jobs
?
Disabling Jobs
?
Enabling Jobs
job任務及與之相關的過程
Job Tasks and Their Procedures
----------------------------------
task procedure
----------------------------------
create a job create_job
alter a job set_atribute
run a job run_job
copy a job copy_job
drop a job drop_job
stop a job stop_job
disable a job disable
enable a job enable
-----------------------------------
creating jobs
SQL> begin
2 dbms_scheduler.create_job(job_name => 'my_job',job_type => 'stored_procedure',
3 job_action=>'proc_filter',start_date => sysdate,repeat_interval => 'freq=daily;interval=1',end_date=>sysdate+2,comments=>'my job comments');
4 end;
5 /
PL/SQL procedure successfully completed
另外重複執行job不會自動刪除,除非指定了end_date或者到達max_failures或max_runs配置值
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
------------------------------ ------------------------------ ----------- ------------------------------ ---------------------------------------------------------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------- -------------------------------------------------------------------------------- ------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------- --------- ----------- --------------- ------------ ---------- ---------- ------------- ------------ ----------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------------------------------------------------------------------------- ------------- -------------------- ------------------- -------------------------------------------------------------------------------- ------ ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ----------- ------------- ----------------------------- -------------------------------------------------------------------------------- ----------
MY_JOB REGULAR SCOTT STORED_PROCEDURE proc_filter 0 CALENDAR 02-FEB-13 10.03.34.000000 PM +08:00 freq=daily;interval=1 04-FEB-13 10.03.34.000000 PM +08:00 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 my job comments 264240
ZXY_JOB REGULAR SCOTT EXECUTABLE c:\t_exp.bat 0 SCOTT MY_SCHEDULE NAMED 01-FEB-13 12.03.49.700000 PM +08:00 DEFAULT_JOB_CLASS TRUE TRUE FALSE SCHEDULED 3 3 3 0 02-FEB-13 07.25.25.329000 PM +08:00 +000000000 00:00:12.704000 03-FEB-13 12.03.49.400000 PM +08:00 OFF FALSE TRUE FALSE 1 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC 1 FALSE FALSE zxy job comment 2098228
SQL>
---關於job的字典,其資訊相關完備,
SQL> desc user_scheduler_jobs;
Name Type Nullable Default Comments
----------------------------- ---------------------------- -------- ------- ---------------------------------------------------------------------------------
JOB_NAME VARCHAR2(30) Y Name of the scheduler job
JOB_SUBNAME VARCHAR2(30) Y Subname of the scheduler job (for a job running a chain step) --涉及到上幾文中所述的chain,step
JOB_STYLE VARCHAR2(11) Y Job style. - regular, lightweight or volatile
JOB_CREATOR VARCHAR2(30) Y Original creator of this job
CLIENT_ID VARCHAR2(64) Y Client id of user creating this job
GLOBAL_UID VARCHAR2(32) Y Global uid of user creating this job
PROGRAM_OWNER VARCHAR2(4000) Y Owner of the program associated with the job ---program
PROGRAM_NAME VARCHAR2(4000) Y Name of the program associated with the job
JOB_TYPE VARCHAR2(16) Y Inlined job action type
JOB_ACTION VARCHAR2(4000) Y Inlined job action
NUMBER_OF_ARGUMENTS NUMBER Y Inlined job number of arguments
SCHEDULE_OWNER VARCHAR2(4000) Y Owner of the schedule that this job uses (can be a window or window group)
SCHEDULE_NAME VARCHAR2(4000) Y Name of the schedule that this job uses (can be a window or window group) ---schedule
SCHEDULE_TYPE VARCHAR2(12) Y Type of the schedule that this job uses
START_DATE TIMESTAMP(6) WITH TIME ZONE Y Original scheduled start date of this job (for an inlined schedule)
REPEAT_INTERVAL VARCHAR2(4000) Y Inlined schedule PL/SQL expression or calendar string
EVENT_QUEUE_OWNER VARCHAR2(30) Y Owner of source queue into which event will be raised
EVENT_QUEUE_NAME VARCHAR2(30) Y Name of source queue into which event will be raised --event,event佇列,採用queue table
EVENT_QUEUE_AGENT VARCHAR2(256) Y Name of AQ agent used by user on the event source queue (if it is a secure queue)
EVENT_CONDITION VARCHAR2(4000) Y Boolean expression used as subscription rule for event on the source queue
EVENT_RULE VARCHAR2(65) Y Name of rule used by the coordinator to trigger event based job
FILE_WATCHER_OWNER VARCHAR2(260) Y Owner of file watcher on which this job is based
FILE_WATCHER_NAME VARCHAR2(260) Y Name of file watcher on which this job is based
END_DATE TIMESTAMP(6) WITH TIME ZONE Y Date after which this job will no longer run (for an inlined schedule)
JOB_CLASS VARCHAR2(30) Y Name of job class associated with the job ---job class
ENABLED VARCHAR2(5) Y Whether the job is enabled
AUTO_DROP VARCHAR2(5) Y Whether this job will be dropped when it has completed ---這個job執行後是否刪除
RESTARTABLE VARCHAR2(5) Y Whether this job can be restarted or not
STATE VARCHAR2(15) Y Current state of the job
JOB_PRIORITY NUMBER Y Priority of the job relative to others within the same class --與job class的關聯
RUN_COUNT NUMBER Y Number of times this job has run
MAX_RUNS NUMBER Y Maximum number of times this job is scheduled to run
FAILURE_COUNT NUMBER Y Number of times this job has failed to run
MAX_FAILURES NUMBER Y Number of times this job will be allowed to fail before being marked broken
RETRY_COUNT NUMBER Y Number of times this job has retried, if it is retrying.
LAST_START_DATE TIMESTAMP(6) WITH TIME ZONE Y Last date on which the job started running
LAST_RUN_DURATION INTERVAL DAY(9) TO SECOND(6) Y How long the job took last time
NEXT_RUN_DATE TIMESTAMP(6) WITH TIME ZONE Y Next date the job is scheduled to run on
SCHEDULE_LIMIT INTERVAL DAY(3) TO SECOND(0) Y Time in minutes after which a job which has not run yet will be rescheduled
MAX_RUN_DURATION INTERVAL DAY(3) TO SECOND(0) Y This column is reserved for future use
LOGGING_LEVEL VARCHAR2(11) Y Amount of logging that will be done pertaining to this job
STOP_ON_WINDOW_CLOSE VARCHAR2(5) Y Whether this job will stop if a window it is associated with closes
INSTANCE_STICKINESS VARCHAR2(5) Y Whether this job is sticky
RAISE_EVENTS VARCHAR2(4000) Y List of job events to raise for this job 與這個job相關的事件列表
SYSTEM VARCHAR2(5) Y Whether this is a system job
JOB_WEIGHT NUMBER Y Weight of this job
NLS_ENV VARCHAR2(4000) Y NLS environment of this job
SOURCE VARCHAR2(128) Y Source global database identifier
NUMBER_OF_DESTINATIONS NUMBER Y
DESTINATION_OWNER VARCHAR2(512) Y Owner of destination object (if used) else NULL
DESTINATION VARCHAR2(512) Y Destination that this job will run on ---job所執行之目的地
CREDENTIAL_OWNER VARCHAR2(30) Y Owner of login credential
CREDENTIAL_NAME VARCHAR2(30) Y Name of login credential
INSTANCE_ID NUMBER Y Instance user requests job to run on. ---job所執行的instance,與rac有關
DEFERRED_DROP VARCHAR2(5) Y Whether this job will be dropped when completed due to user request.
ALLOW_RUNS_IN_RESTRICTED_MODE VARCHAR2(5) Y
COMMENTS VARCHAR2(240) Y Comments on the job
FLAGS NUMBER Y This column is for internal use.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-754404/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dbms_scheduler package系列(六)Package
- dbms_scheduler package系列(五)Package
- dbms_scheduler package系列三Package
- dbms_scheduler package系列(二)Package
- dbms_scheduler package系列(一)Package
- dbms_scheduler package系列(七)-2Package
- dbms_scheduler package系列(七)-1Package
- dbms_scheduler package body INVALID 解決方案Package
- dart系列之:建立Library packageDartPackage
- Oracle DBMS_SCHEDULEROracle
- Flutter系列（四）——HelloWorldFlutter
- not an rpm package (or package manifest):Package
- not an rpm package (or package manifest)Package
- packagePackage
- 使用oracle dbms_scheduler代替crontabOracle
- dbms_scheduler 相關資料
- Package Specification 和 Package Body 及 Package有什麼區別？ (轉)Package
- 前端踩坑系列《四》前端
- dbms_mview系列(四)View
- 深入安卓Package Manager和Package Installer安卓Package
- Oracle定時任務dbms_schedulerOracle
- 建立packagePackage
- Package ssh is not available, but is referred to by another package 錯誤PackageAI
- 《xhtml入門系列》之四HTML
- docker系列（四）：資料卷Docker
- Pytorch系列:（四）IO操作PyTorch
- 執行緒系列四AQS執行緒AQS
- vue系列生命週期（四）Vue
- WebAssembly 系列（四）WebAssembly 工作原理Web
- RxJava操作符系列四RxJava
- oracle排程程式作業dbms_schedulerOracle
- oracle使用DBMS_SCHEDULER排程作業Oracle
- 使用 DBMS_SCHEDULER執行外部命令
- Oracle 排程程式作業( dbms_scheduler )Oracle
- ORA-04042 procedure, function, package, or package body does not existFunctionPackage
- java 包（package）JavaPackage
- alter package/procedurePackage
- Package and ClassPath (轉)Package