使用dbms_job包来实现数据库后台进程
来源:互联网 发布:淘宝上有成人杂志吗 编辑:程序博客网 时间:2024/05/16 15:02
1建立实现任务的过程
在schema manager或SQL PLUS里建立如下过程
CREATE OR REPLACE PROCEDURE "CUSTOMER"."T_JOBTEST" as
begin
update emp set active =0
where active =1
and date_published < sysdate - active_days;
end ;
2 向任务队列中加入任务
在SQL PLUS中执行下列script
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno, 't_jobtest();', SYSDATE, 'SYSDATE + 1');
commit;
end;
该任务立即执行(SYSDATE),并且每隔一天执行一次('SYSDATE + 1')。
3 查询此任务是否加入任务队列
在SQL PLUS中执行下列script
SELECT job, next_date, next_sec, failures, broken
FROM user_jobs;
------------------
DBMS_JOB 包介绍
调度任务队列里的任务要使用DBMS_JOB包中的过程。使用任务队列不需要特别的数据库特权。任何可以使用这些过程的用户都可以使用任务队列。
Table 8-2 DBMS_JOB包中的过程
Procedure
Description
Described
SUBMIT
Submits a job to the job queue. 向任务队列提交一个任务
REMOVE
Removes a specified job from the job queue. 从任务队列中删除指定的任务
CHANGE
Alters a specified job. You can alter the job description, the time at which the job will be run, or the interval between executions of the job. 改变任务
WHAT
Alters the job description for a specified job. 改变指定任务的任务内容
NEXT_DATE
Alters the next execution time for a specified job. 改变指定任务的下一次执行时间
INTERVAL
Alters the interval between executions for a specified job. 改变指定任务的执行时间间隔。
BROKEN
Disables job execution. If a job is marked as broken, Oracle does not attempt to execute it. 禁止指定任务的执行
RUN
Forces a specified job to run. 强制执行指定的任务
Submitting a Job to the Job Queue 向任务队列提交一个任务
To submit a new job to the job queue, use the SUBMIT procedure in the DBMS_JOB package:
DBMS_JOB.SUBMIT( job OUT BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE DEFAULT SYSDATE, interval IN VARCHAR2 DEFAULT 'null', no_parse IN BOOLEAN DEFAULT FALSE)
The SUBMIT procedure returns the number of the job you submitted. Table 8-3 describes the procedure's parameters.
Table 8-3 DBMS_JOB.SUBMIT 的参数
Parameter
Description
job
This is the identifier assigned to the job you created. You must use the job number whenever you want to alter or remove the job. For more information about job numbers, see "Job Numbers".
what
This is the PL/SQL code you want to have executed. 这里是你想执行的PL/SQL代码
For more information about defining a job, see "Job Definitions".
next_date
This is the next date when the job will be run. The default value is SYSDATE.
interval
This is the date function that calculates the next time to execute the job. The default value is NULL. INTERVAL must evaluate to a future point in time or NULL.
For more information on how to specify an execution interval, see "Job Execution Interval".
no_parse
This is a flag. The default value is FALSE.
If NO_PARSE is set to FALSE (the default), Oracle parses the procedure associated with the job. If NO_PARSE is set to TRUE, Oracle parses the procedure associated with the job the first time that the job is executed. If, for example, you want to submit a job before you have created the tables associated with the job, set NO_PARSE to TRUE.
As an example, let's submit a new job to the job queue. The job calls the procedure DBMS_DDL.ANALYZE_OBJECT to generate optimizer statistics for the table DQUON.ACCOUNTS. The statistics are based on a sample of half the rows of the ACCOUNTS table. The job is run every 24 hours:
VARIABLE jobno number;begin 2> DBMS_JOB.SUBMIT(:jobno, 3> 'dbms_ddl.analyze_object(''TABLE'', 4> ''DQUON'', ''ACCOUNTS'', 5> ''ESTIMATE'', NULL, 50);' 6> SYSDATE, 'SYSDATE + 1'); 7> commit; 8> end; 9> /Statement processed.print jobnoJOBNO----------14144
Job Definition 任务定义
任务定义就是SUBMIT过程中WHAT参数中指定的PL/SQL代码。
通常任务定义(内容)是一个过程的一个调用。这个过程能有任意数量的参数。
Note:
In the job definition, use two single quotation marks around strings. Always include a semicolon at the end of the job definition. 在任务定义中,用2个单引号包围字符串。任务定义的末尾总是带一个分号。
Jobs and Import/Export
Jobs can be exported and imported. Thus, if you define a job in one database, you can transfer it to another database. When exporting and importing jobs, the job's number, environment, and definition remain unchanged.
任务是可以被卸出(exported )卸入(imported)的。
Job Execution Interval 任务的执行间隔
The INTERVAL date function is evaluated immediately before a job is executed. If the job completes successfully, the date calculated from INTERVAL becomes the new NEXT_DATE. If the INTERVAL date function evaluates to NULL and the job completes successfully, the job is deleted from the queue.
If a job should be executed periodically at a set interval, use a date expression similar to 'SYSDATE + 7' in the INTERVAL
Table 8-5 Common Job Execution Intervals
Date Expression Evaluation 'SYSDATE + 7'exactly seven days from the last execution
最后一次执行的7天之后执行
'SYSDATE + 1/48'every half hour
每半个小时执行一次
'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24'every Monday at 3PM
每个礼拜一的下午3点执行
'NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ''Q''), 3), ''THURSDAY'')'first Thursday of each quarter 每个季度的第一个星期四
Note:When specifying NEXT_DATE or INTERVAL, remember that date literals and strings must be enclosed in single quotation marks. Also, the value of INTERVAL must be enclosed in single quotation marks.
Removing a Job from the Job Queue 删除任务队列中的任务
To remove a job from the job queue, use the REMOVE procedure in the DBMS_JOB package:
The following statement removes job number 14144 from the job queue:
DBMS_JOB.REMOVE(14144);
Syntax for WHAT
You can alter the definition of a job by calling the DBMS_JOB.WHAT procedure. Table 8-3 describes the procedure's parameters.
DBMS_JOB.WHAT( job IN BINARY_INTEGER, what IN VARCHAR2)
Note:
When you execute procedure WHAT, Oracle records your current environment. This becomes the new environment for the job.
Syntax for NEXT_DATE
You can alter the next date that Oracle executes a job by calling the DBMS_JOB.NEXT_DATE procedure. Table 8-3 describes the procedure's parameters.
DBMS_JOB.NEXT_DATE( job IN BINARY_INTEGER,next_date IN DATE)
Syntax for INTERVAL
You can alter the execution interval of a job by calling the DBMS_JOB.INTERVAL procedure. Table 8-3 describes the procedure's parameters.
DBMS_JOB.INTERVAL( job IN BINARY_INTEGER, interval IN VARCHAR2)
Viewing Job Queue Information 查询任务队列的信息
View DescriptionDBA_JOBSLists all the jobs in the database. 列出数据库中的任务USER_JOBSLists all jobs owned by the user. 列出该用户拥有的任务DBA_JOBS_RUNNINGLists all jobs in the database that are currently running. This view joins V$LOCK and JOB$. 列出当前运行的任务。
For example, you can display information about a job's status and failed executions. The following sample query creates a listing of the job number, next execution time, failures, and broken status for each job you have submitted:
显示任务的状态和失败执行的次数。
SELECT job, next_date, next_sec, failures, broken FROM user_jobs;JOB NEXT_DATE NEXT_SEC FAILURES B---------- --------- -------- ---------- - 9125 01-NOV-94 00:00:00 4 N 14144 24-OCT-94 16:35:35 0 N 41762 01-JAN-00 00:00:00 16 Y3 rows selected.
You can also display information about jobs currently running. The following sample query lists the session identifier, job number, user who submitted the job, and the start times for all currently running jobs:
显示当前运行的任务的信息。
SELECT sid, r.job, log_user, r.this_date, r.this_sec FROM dba_jobs_running r, dba_jobs j WHERE r.job = j.job;SID JOB LOG_USER THIS_DATE THIS_SEC---------- ---------- -------------------- --------- -------- 12 14144 JFEE 24-OCT-94 17:21:24 25 8536 SCOTT 24-OCT-94 16:45:122 rows selected.
转载者新增:parameter. For example, if you set the execution interval to 'SYSDATE + 7' on Monday, but for some reason (such as a network failure) the job is notexecuted until Thursday, 'SYSDATE + 7' then executes every Thursday, not Monday.
实际开发中可能会遇到要一个月的某个时间定期执行脚本的需求,可以使用
Add_months(to_date(sysdate,''yyyymmdd''), 1)来实现,这个脚本就
会一个月执行一次。
例如:VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno, 'p_procedure;',to_date(sysdate,'yyyymmdd'), 'Add_months(to_date('sysdate',''yyyymmdd''), 1)');
commit;
end;
print jobno;
- 使用dbms_job包来实现数据库后台进程
- 使用dbms_job包来实现数据库后台进程
- 使用DBMS_JOB来调度作业
- oracle包dbms_job的使用
- oracle包dbms_job的使用
- oracle包dbms_job的使用
- oracle包dbms_job的使用
- oracle包dbms_job的使用
- DBMS_JOB包
- DBMS_JOB包
- dbms_job包
- Oracle中dbms_job包的使用
- 使用DBMS_JOB包创建ORACLE定时任务
- 使用DBMS_JOB包创建ORACLE定时任务
- PL/SQL:学会使用DBMS_JOB包
- 使用DBMS_JOB包创建ORACLE定时任务
- 如何使用dbms_job系统包管理job
- 使用DBMS_JOB包创建ORACLE定时任务
- web上使用下拉框
- grub全面接触
- 中国月历
- SQLSERVER的数据转换服务DTS的调用代码
- 中国月历
- 使用dbms_job包来实现数据库后台进程
- 预防"提交"按钮的多次点击
- 集中分布式搜索引擎的4种设计方案
- 如何在用户关掉web浏览器窗口前, 进行相应的动作
- 分布式搜索引擎
- 编写高性能 Web 应用程序的 10 个技巧
- 微软联手麦当劳 致力于其下一代销售点平台优化操作
- Beijing Hutong Tour
- vc获取用户名和口令Windows