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博客 ” ，链接：https://blog.itpub.net/9240380/viewspace-754404/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- redhat要持续学习哟加加油
- 升级redhat 6.8 默认gdb 7.2到gdb 8.3.1操作指南
- redhat 6.8升级默认cmake 2.8到cmake 3.9
- mysql source code源代安装的前提条件requirement
- mysql handler语句之一
- mysql show open tables相关知识体系之一
- mysql snapshot read快照读及current read当前读与锁lock之一
- mysql 5.7 _rowid虚列之一
- mysql innodb lock锁之record lock之一
- mysql隐式提交事务transaction一点笔记
