首页 / 更多教程 / dbms_scheduler create_job repeat_interval 列子

dbms_scheduler create_job repeat_interval 列子

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了dbms_scheduler create_job repeat_interval 列子，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9004字，纯文字阅读大概需要13分钟。

内容图文

dbms_scheduler create_job repeat_interval 列子

Setting the Repeat Interval

You can control when and how often a job repeats.

About Setting the Repeat Interval
You control when and how often a job repeats by setting the repeat_interval attribute of the job itself or the named schedule that the job references. You can set repeat_interval with DBMS_SCHEDULER package procedures or with Cloud Control.
Using the Scheduler Calendaring Syntax
The main way to set how often a job repeats is to set the repeat_interval attribute with a Scheduler calendaring expression.
Using a PL/SQL Expression
When you need more complicated capabilities than the calendaring syntax provides, you can use PL/SQL expressions. You cannot, however, use PL/SQL expressions for windows or in named schedules. The PL/SQL expression must evaluate to a date or a timestamp.
Differences Between PL/SQL Expression and Calendaring Syntax Behavior
There are important differences in behavior between a calendaring expression and PL/SQL repeat interval.
Repeat Intervals and Daylight Savings
For repeating jobs, the next time a job is scheduled to run is stored in a timestamp with time zone column.

29.4.5.1 About Setting the Repeat Interval

You control when and how often a job repeats by setting the repeat_interval attribute of the job itself or the named schedule that the job references. You can set repeat_interval with DBMS_SCHEDULER package procedures or with Cloud Control.

Evaluating the repeat_interval results in a set of timestamps. The Scheduler runs the job at each timestamp. Note that the start date from the job or schedule also helps determine the resulting set of timestamps. If no value for repeat_interval is specified, the job runs only once at the specified start date.

Immediately after a job starts, the repeat_interval is evaluated to determine the next scheduled execution time of the job. While this might arrive while the job is still running, a new instance of the job does not start until the current one completes.

29.4.5.2 Using the Scheduler Calendaring Syntax

The main way to set how often a job repeats is to set the repeat_interval attribute with a Scheduler calendaring expression.

See Also:

Oracle Database PL/SQL Packages and Types Reference for a detailed description of the calendaring syntax for repeat_interval as well as the CREATE_SCHEDULE procedure

Examples of Calendaring Expressions

The following examples illustrate simple repeat intervals. For simplicity, it is assumed that there is no contribution to the evaluation results by the start date.

Run every Friday. (All three examples are equivalent.)

FREQ=DAILY; BYDAY=FRI;
FREQ=WEEKLY; BYDAY=FRI;
FREQ=YEARLY; BYDAY=FRI;

Run every other Friday.

FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;

Run on the last day of every month.

FREQ=MONTHLY; BYMONTHDAY=-1;

Run on the next to last day of every month.

FREQ=MONTHLY; BYMONTHDAY=-2;

Run on March 10th. (Both examples are equivalent)

FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;
FREQ=YEARLY; BYDATE=0310;

Run every 10 days.

FREQ=DAILY; INTERVAL=10;

Run daily at 4, 5, and 6PM.

FREQ=DAILY; BYHOUR=16,17,18;

Run on the 15th day of every other month.

FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;

Run on the 29th day of every month.

FREQ=MONTHLY; BYMONTHDAY=29;

Run on the second Wednesday of each month.

FREQ=MONTHLY; BYDAY=2WED;

Run on the last Friday of the year.

FREQ=YEARLY; BYDAY=-1FRI;

Run every 50 hours.

FREQ=HOURLY; INTERVAL=50;

Run on the last day of every other month.

FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=-1;

Run hourly for the first three days of every month.

FREQ=HOURLY; BYMONTHDAY=1,2,3;

Here are some more complex repeat intervals:

Run on the last workday of every month (assuming that workdays are Monday through Friday).

FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1

Run on the last workday of every month, excluding company holidays. (This example references an existing named schedule called Company_Holidays.)

FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; EXCLUDE=Company_Holidays; BYSETPOS=-1

Run at noon every Friday and on company holidays.

FREQ=YEARLY;BYDAY=FRI;BYHOUR=12;INCLUDE=Company_Holidays

Run on these three holidays: July 4th, Memorial Day, and Labor Day. (This example references three existing named schedules, JUL4, MEM, and LAB, where each defines a single date corresponding to a holiday.)

JUL4,MEM,LAB

Examples of Calendaring Expression Evaluation

A repeat interval of "FREQ=MINUTELY;INTERVAL=2;BYHOUR=17; BYMINUTE=2,4,5,50,51,7;" with a start date of 28-FEB-2004 23:00:00 will generate the following schedule:

SUN 29-FEB-2004 17:02:00
SUN 29-FEB-2004 17:04:00
SUN 29-FEB-2004 17:50:00
MON 01-MAR-2004 17:02:00
MON 01-MAR-2004 17:04:00
MON 01-MAR-2004 17:50:00
...

A repeat interval of "FREQ=MONTHLY;BYMONTHDAY=15,-1" with a start date of 29-DEC-2003 9:00:00 will generate the following schedule:

WED 31-DEC-2003 09:00:00
THU 15-JAN-2004 09:00:00
SAT 31-JAN-2004 09:00:00
SUN 15-FEB-2004 09:00:00
SUN 29-FEB-2004 09:00:00
MON 15-MAR-2004 09:00:00
WED 31-MAR-2004 09:00:00
...

A repeat interval of "FREQ=MONTHLY;" with a start date of 29-DEC-2003 9:00:00 will generate the following schedule. (Note that because there is no BYMONTHDAY clause, the day of month is retrieved from the start date.)

MON 29-DEC-2003 09:00:00
THU 29-JAN-2004 09:00:00
SUN 29-FEB-2004 09:00:00
MON 29-MAR-2004 09:00:00
...

Example of Using a Calendaring Expression

As an example of using the calendaring syntax, consider the following statement:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name             => ‘scott.my_job1‘,
   start_date           => ‘15-JUL-04 01.00.00 AM Europe/Warsaw‘,
   repeat_interval      => ‘FREQ=MINUTELY; INTERVAL=30;‘,
   end_date             => ‘15-SEP-04 01.00.00 AM Europe/Warsaw‘,
   comments             => ‘My comments here‘);
END;
/

This creates my_job1 in scott. It will run for the first time on July 15th and then run until September 15. The job is run every 30 minutes.

29.4.5.3 Using a PL/SQL Expression

When you need more complicated capabilities than the calendaring syntax provides, you can use PL/SQL expressions. You cannot, however, use PL/SQL expressions for windows or in named schedules. The PL/SQL expression must evaluate to a date or a timestamp.

Other than this restriction, there are no limitations, so with sufficient programming, you can create every possible repeat interval. As an example, consider the following statement:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name             => ‘scott.my_job2‘, 
   start_date           => ‘15-JUL-04 01.00.00 AM Europe/Warsaw‘,
   repeat_interval      => ‘SYSTIMESTAMP + INTERVAL ‘30‘ MINUTE‘,
   end_date             => ‘15-SEP-04 01.00.00 AM Europe/Warsaw‘,
   comments             => ‘My comments here‘);
END;
/

This creates my_job1 in scott. It will run for the first time on July 15th and then every 30 minutes until September 15. The job is run every 30 minutes because repeat_interval is set to SYSTIMESTAMP + INTERVAL ‘30‘ MINUTE, which returns a date 30 minutes into the future.

29.4.5.4 Differences Between PL/SQL Expression and Calendaring Syntax Behavior

There are important differences in behavior between a calendaring expression and PL/SQL repeat interval.

These differences include the following:

Start date
- Using the calendaring syntax, the start date is a reference date only. Therefore, the schedule is valid as of this date. It does not mean that the job will start on the start date.
- Using a PL/SQL expression, the start date represents the actual time that the job will start executing for the first time.

Next run time
As an example of the difference, for a job that is scheduled to start at 2:00 PM and repeat every 2 hours, but actually starts at 2:10:
- If calendaring syntax specified the repeat interval, then it would repeat at 4, 6 and so on.
- If a PL/SQL expression is used, then the job would repeat at 4:10, and if the next job actually started at 4:11, then the subsequent run would be at 6:11.
- Using the calendaring syntax, the next time the job runs is fixed.
- Using the PL/SQL expression, the next time the job runs depends on the actual start time of the current job run.

To illustrate these two points, consider a situation where you have a start date of 15-July-2003 1:45:00 and you want it to repeat every two hours. A calendar expression of "FREQ=HOURLY; INTERVAL=2; BYMINUTE=0;" will generate the following schedule:

TUE 15-JUL-2003  03:00:00
TUE 15-JUL-2003  05:00:00
TUE 15-JUL-2003  07:00:00
TUE 15-JUL-2003  09:00:00
TUE 15-JUL-2003  11:00:00
...

Note that the calendar expression repeats every two hours on the hour.

A PL/SQL expression of "SYSTIMESTAMP + interval ‘2‘ hour", however, might have a run time of the following:

TUE 15-JUL-2003  01:45:00
TUE 15-JUL-2003  03:45:05
TUE 15-JUL-2003  05:45:09
TUE 15-JUL-2003  07:45:14
TUE 15-JUL-2003  09:45:20
...

dbms_scheduler create_job repeat_interval 列子

标签：oracle

本文系统来源：http://291268154.blog.51cto.com/8955907/1944501

内容总结

以上是互联网集市为您收集整理的dbms_scheduler create_job repeat_interval 列子全部内容，希望文章能够帮你解决dbms_scheduler create_job repeat_interval 列子所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/485633.html

来源：【匿名】

【上一篇】wallet病毒解密 x3m 勒索敲诈病毒文件恢复 SQL MDF BAK 数据库修复解密【下一篇】关于IE的RegExp.exec的问题

更多 ►

【dbms_scheduler create_job repeat_interval 列子】教程文章相关的互联网学习教程文章

Django(博客系统)：按照时间分层筛选“/blog/article/?create_time__year=2017”，出现问题：Database returned an invalid datetime value. Are time zone definitions for your database installed?【代码】【图】

create_time‘ # 详细时间分层筛选models.Article中create_time定义如下：create_time = models.DateTimeField(u‘创建时间‘, auto_now_add=True)设置后，后台呈现效果：按照时间按分层筛选时，出现了错误：ValueError at /admin/blog/article/ Database returned an invalid datetime value. Are time zone definitions for your database installed? Request Method: GET Request URL: http://127.0...

关于epoll_create的size，以及创建出的句柄eploofd是否需要close探讨【代码】

size epoll_creat的函数圆形如下： int epoll_create(int size); 其中，size的含义值得琢磨。首先参考官方给出的资料： gun库里的注释 /* Creates an epoll instance. Returns an fd for the new instance.The "size" parameter is a hint specifying the number of filedescriptors to be associated with the new instance. The fdreturned by epoll_create() should be closed with close(). */ extern int epoll_create (in...

通过dbcp链接池对数据库操作报 Cannot create PoolableConnectionFactory (Could not create connection to database server. Attempted reconnect 3 times. Giving up.)--解决方案

at sun.security.ssl.InputRecord.readV3Record(InputRecord.java:394) at sun.security.ssl.InputRecord.read(InputRecord.java:376) at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:927) at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1328) at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1355) at sun.security.ssl.SSLSocketImpl.startHandshake...

SQL——CREATE、ALTER、DROP和VIEW

- 创建新数据库语法：CREATE DATABASE database_nameALTER DATABASE - 修改数据库 CREATE TABLE - 创建新表语法：CREATE TABLE 表名称 ( 列名称1 数据类型, 列名称2 数据类型, 列名称3 数据类型, .... ) 部分数据类型： decimal(size,d)、numeric(size,d) -- 容纳带有小数的数字。"size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数。...

Error while performing database login with the sqljdbc driver:Unable to create connection. Check your URL.【图】

从微软官网下载jdbc驱动包sqljdbc4，运行sqljdbc_4.0.2206.100_chs.exe，将驱动包解压到了Microsoft JDBC Driver 4.0 for SQL Server文件夹下，在Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\chs\auth下有两个文件夹x64和x86，这两个文件夹下都有一个sqljdbc_auth.dll文件，将x64文件夹里面的sqljdbc_auth.dll文件拷贝到windows\system32下，将x86文件夹下的sqljdbc_auth.dll拷贝到windows\sysWOW64下，为什么这样做？ ...

EF6：在多语句事务内不允许使用 CREATE DATABASE 语句

‘/‘ Application.在多语句事务内不允许使用 CREATE DATABASE 语句。Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: 在多语句事务内不允许使用 CREATE DATABASE 语句。Source Error: Line 32: */ ...

Gbase rmt远程导出时提示can’t create/write

GBase的rmt导出功能只支持导出express引擎的表，系统表tables是MEMORY引擎，所以需要导出tables的话就得新建同结构的表，然后再进行转储导出。创建同结构的express引擎临时表：CREATE TABLE testdb.tables ( TABLE_CATALOG varchar(512) DEFAULT NULL, TABLE_SCHEMA varchar(64) NOT NULL DEFAULT ‘’, TABLE_NAME varchar(64) NOT NULL DEFAULT ‘’, TABLE_TYPE varchar(64) NOT NULL DEFAULT ‘’, ENGINE varchar(64) DEFAUL...

SQL基础语法—create index语句

1 create index语句介绍 create index语句用来在表中创建索引。索引的作用提高数据库查询的效率，这个作用对用户来说是透明的，其作用只是对MySQL引擎来说的。 Syntax: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name[index_type]ON tbl_name (index_col_name,...)[index_option][algorithm_option | lock_option] ...index_col_name:col_name [(length)] [ASC | DESC]index_option:KEY_BLOCK_SIZE [=] value| index_type| WI...

Django批量导入数据库-bulk_create()【代码】

Django批量导入数据库-bulk_create()if request.method == 'POST':text_list=request.POST.getlist('text')pwd_list = request.POST.getlist('pwd')datas = []for text,pwd in zip(text_list,pwd_list):obj = User(name=text,password=pwd)datas.append(obj)User.objects.bulk_create(datas)return redirect('index')上述代码是假设前端发来多组数据用request.POST.getlist()获取前端form发来的数据，返回的类型为列表类型用zip()...

无法理解以下程序中的pthread_create()行为？【代码】

#include <stdio.h> #include <pthread.h>void *thread_func(void *arg) {printf("hello, world \n");return 0; }int main(void) {pthread_t t1, t2;pthread_create(&t1, NULL, thread_func, NULL);pthread_create(&t2, NULL, thread_func, NULL);printf("t1 = %d\n",t1);printf("t2 = %d\n",t2);return 0; }上面的程序创建了两个线程,每个线程打印“Hello World”. 所以,根据我的理解,“Hello world”应该打印最多2次. 但是,在多次...

[08001] Could not create connection to database server. Attempted reconnect 3 time ... more 针【图】

针对IDEA连接mysql的问题开门见山的说，我这个连接不上mysql的问题是因为我的mysql版本为5.1 而mysql驱动后面的版本是不兼容这个版本的。在配置好数据库后，默认下载的驱动版本是8.0的版本所以就会造成连接时间超时的问题。我们可以看到，他这里贴心的为我们专门准备了一个5.1的版本，所以我们只需要配置这个驱动的位置即可在连接时，选好对应的驱动即可连接成功！！！

ArcCatalog连接ArcSDE连接报：unable to create new database connection file,permission is denied【图】

参考博文：链接另外，若机器ip变更，sde是否受影响的问题 ArcCatalog连接ArcSDE连接报：unable to create new database connection file,permission is denied标签：comment cti 变更 mission http 技术 catalog ssi details 本文系统来源：http://www.cnblogs.com/mumu122GIS/p/7239787.html

create table as 和create table like的区别【代码】

create table as 和create table like的区别对于MySQL的复制相同表结构方法，有create table as 和create table like 两种，区别是什么呢？/* -- 没有开启gtid的情况下,不拷贝数据，只创建一模一样的表结构，包括索引约束等，结合insert语句可以实现复制一个表的结构和数据的目的 create table tbl_test_bak like tbl_test; insert into tbl_test_bak select * from tbl_test;-- 以下方式也可以创建表结构,包含数据，但是没有索引...