Oracle中如何定时执行一条SQL语句

通过网上查询，找到一种方案，就是先在oracle里面对要定时的sql写成存储过程，再用DBMS_scheduler对存储过程进行定时执行。

创新互联是一家专业提供静乐企业网站建设,专注与网站设计、成都网站建设、成都h5网站建设、小程序制作等业务。10年已为静乐众多企业、政府机构等服务。创新互联专业网站设计公司优惠进行中。

在数据库中新建了一个表MY_JOB_TEST

在数据库中新建了一个表MY_JOB_TEST

在数据库中新建了一个表MY_JOB_TEST

在PLSQL中，执行下面语句模拟存储过程的调用，执行后要点提交才有反应

[sql] view plain copy

BEGIN

INSERT INTO MY_JOB_TEST (NUM) VALUES (1);

END;

然后下面建立一个存储过程JOB_TEST

（注意，这里用户需要CREATE JOB权限，可以用超级管理员用户执行下面语句给指定用户赋予该权限）

[sql] view plain copy

Grant Create Job To 指定用户名

[sql] view plain copy

SQLCREATE OR REPLACE PROCEDURE

JOB_TEST

BEGIN

INSERT INTO MY_JOB_TEST (NUM) VALUES (1);

END;

(此处要执行存储过程不能用exec JB_TEST，这种执行方式只能在命令行中使用)

使用DBMS_SCHEDULER进行定时，这里为每分钟执行一次

[sql] view plain copy

SQL BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name           =  'SCHEDULER_TEST',

job_type           =  'STORED_PROCEDURE',

job_action         =  'JOB_TEST',

start_date         =  sysdate,

repeat_interval    =  'FREQ=MINUTELY;INTERVAL=1');

END;

但是这样子定时任务并不会执行。

我们可以用下面的命令查看一下scheduler的状态

[plain] view plain copy

SQLSELECT * FROM USER_SCHEDULER_JOBS;

此时我们可以看到enable的状态是false的，因此我们需要去启动定时任务

[plain] view plain copy

SQLBEGIN

DBMS_SCHEDULER.ENABLE('SCHEDULER_TEST');

END

dbms_scheduler.enable('j_test');  --启用jobs   

dbms_scheduler.disable('j_test');  --禁用jobs    

dbms_scheduler.run_job('j_test');  --执行jobs    

dbms_scheduler.stop_job('j_test');  --停止jobs   

dbms_scheduler.drop_job('j_test');  --删除jobs  

然后再查询job的enable装态，发现为true了。

然后查看MY_JOB_TEST表，发现每分钟会往里面添加记录。

当需要修改定时任务或者调度的其他属性时，可以用下面的

dbms_scheduler.set_attribute('调度名','调度属性','调度值');

至此，定时任务完成。

怎么查看oracle10g数据库的定时任务

--当前用户定时任务

select count(*) from user_jobs;

--有权限访问的定时任务

select count(*) from all_jobs;

--整个数据库所有定时任务

select count(*) from dba_jobs;

如何获得oracle物化视图 定时刷新时 刷新所用的时间 或者是监控 物化视图在jobs中建立的job任务的执行时间

你确定你搜了么。。。

以下是物化视图的建立，注意是refresh mode是 on demand还是on commit

ON DEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性;而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。

创建物化视图时未作指定，则Oracle按ON DEMAND模式来创建。

但怎么更新?或者说物化视图的数据怎么随着基表而更新?Oracle提供了两种方式，手工刷新和自动刷新，像我们这种，在物化视图定义时，未作任何指定，那当然是默认的手工刷新了。也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。

所谓的自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包

CREATE MATERIALIZED VIEW mview_name

[TABLESPACE ts_name]

[PARALLEL (DEGREE n)]

[BUILD {IMMEDIATE|DEFERRED}]

[{ REFRESH {FAST|COMPLETE|FORCE}

[{ON COMMIT|ON DEMAND}]

| NEVER REFRESH } ]

[{ENABLE|DISABLE} QUERY REWRITE]

AS SELECT … FROM …

基于你说的情况，有job的定时刷新，应该是on commit的情况，或者是手动建立的job

下面给出我的测试：

create materialized view scott.MV_test_index

refresh force on commit

as

select * from scott.test_index;

SQL select count(*) from mv_test_index;

COUNT(*)

----------

50195

SQL delete from test_index where rownum=1;

1 row deleted.

SQL commit;

Commit complete.

SQL select count(*) from mv_test_index;

COUNT(*)

----------

50194

这里我们发现commit的时间会有些长，因为同时要刷新物化视图，我们这里其实是重新做了删除物化视图数据和插入新数据的操作（因为刷新的方法是complete，当然你可以选择FAST来做增量刷新，而且fast是首选），然后物化视图已经更新了。

下面是开启session sql trace的结构，你可以看到commit之后oralce所做的操作。

********************************************************************************

commit

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.11 0.23 0 0 1 0

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 0.11 0.23 0 0 1 0

Misses in library cache during parse: 0

Parsing user id: 54

********************************************************************************

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE

NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false')

NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"),

NVL(SUM(C2),:"SYS_B_1")

FROM

(SELECT /*+ NO_PARALLEL("MV_TEST_INDEX") FULL("MV_TEST_INDEX")

NO_PARALLEL_INDEX("MV_TEST_INDEX") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2

FROM "SCOTT"."MV_TEST_INDEX" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5") SEED

(:"SYS_B_6") "MV_TEST_INDEX") SAMPLESUB

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 1 0.07 0.19 44 69 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 3 0.07 0.19 44 69 0 1

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 54 (recursive depth: 2)

Rows Row Source Operation

------- ---------------------------------------------------

1 SORT AGGREGATE (cr=69 pr=44 pw=0 time=196762 us)

10624 MAT_VIEW ACCESS SAMPLE MV_TEST_INDEX (cr=69 pr=44 pw=0 time=1853189 us)

********************************************************************************

delete from "SCOTT"."MV_TEST_INDEX"

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.02 0.01 0 3 0 0

Execute 1 7.48 9.46 20 321 154984 50194

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 7.50 9.48 20 324 154984 50194

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 54 (recursive depth: 1)

Rows Row Source Operation

------- ---------------------------------------------------

0 DELETE MV_TEST_INDEX (cr=371 pr=20 pw=0 time=9503556 us)

50194 MAT_VIEW ACCESS FULL MV_TEST_INDEX (cr=310 pr=20 pw=0 time=1355940 us)

********************************************************************************

INSERT /*+ */ INTO "SCOTT"."MV_TEST_INDEX"("OBJECT_ID","OBJECT_NAME") SELECT

"TEST_INDEX"."OBJECT_ID","TEST_INDEX"."OBJECT_NAME" FROM

"SCOTT"."TEST_INDEX" "TEST_INDEX"

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.01 0.01 0 0 0 0

Execute 1 5.90 12.87 0 777 103790 50193

Fetch 0 0.00 0.00 0 0 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 2 5.91 12.88 0 777 103790 50193

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 54 (recursive depth: 1)

Rows Row Source Operation

------- ---------------------------------------------------

50193 TABLE ACCESS FULL TEST_INDEX (cr=241 pr=0 pw=0 time=602379 us)

********************************************************************************

BEGIN dbms_session.set_sql_trace(false); END;

接下来看看fast的刷新方法：

CREATE MATERIALIZED VIEW LOG ON scott.test_index; --这里我试过yantinkun的那个网页，是不能加with rowid的，会报错：ORA-23415: materialized view log for does not record the primary key

DROP materialized view log on scott.test_index;

create materialized view scott.MV_test_index

refresh fast on commit

as

select * from scott.test_index;

SQL exec dbms_session.set_sql_trace(true);

PL/SQL procedure successfully completed.

SQL delete from test_index where rownum=1;

1 row deleted.

SQL commit;

Commit complete.

SQL exec dbms_session.set_sql_trace(false);

PL/SQL procedure successfully completed.

SQL host

这次的commit快多了，因为只是增量的刷新；

看下log中有这样一行：

DELETE FROM "SCOTT"."MV_TEST_INDEX" SNAP$

WHERE

"OBJECT_ID" = :1

只是找到了多出来的那个object的id，然后从物化视图里删掉就ok了。

可以查看物化视图的开始刷新时间，通过查看视图

select * from mlog$ where mowner='SCOTT' and MASTER='TEST_INDEX'来查找对应建立的物化视图的log，我这里查出来的是scott.MLOG$_TEST_INDEX

然后执行查询就可以看到详细的信息了：

select * from scott.MLOG$_TEST_INDEX

然后是刷新的时间，当刷新选项是commit的时候你是没法看的，只能从session的trace里面估计，

on demand话就是你自定义的job 来执行exec dbms_mview.refresh('MV_TEST_INDEX)

这个是可以看的，可以从user_jobs这个视图来看，里面有个total_time来记录job的执行总时间。

oracle 定时任务问题，定时任务没有成功 定时任务如下：

1.引言

定制定时执行的任务有两种形式，系统级别和数据库级别，

从操作系统级别来讲，

windows系统我们可以使用任务计划来实现，

对于winXP系统，设置步骤如下,开始---设置---控制面板---任务计划，然后添加任务计划，按向导完成配置即可;

对于win7系统，设置步骤如下，点击开始,然后在 搜索程序和文件 框中输入 [任务计划]，然后点击出来的任务计划程序，创建一个基本任务即可；

linux系统我们可以使用crontab命令来是实现，

关于crontab命令的使用可以见之前的博客（）；

从数据库级别来讲，我们可以采用数据库的job来实现；

本节主要介绍通过oracle数据库的job来定制一个简单的定时执行任务。本节会采用oracle定制一个定时向一个表中插入语句。

这里聊一下我做这个oracle定时任务的缘由：

项目中要在固定的时间，把一个数据库中某些表的数据同步到另外一个数据库，显然这些工作我不能每次都自己去做，

那么，我要怎么来做呢？这个时候oracle的定时执行任务JOB无疑是我的最佳选择。我把同步的脚本放在一个存储过程中，

然后在固定的时间去执行这个存储过程就OK了。

注意：以下所有的操作都是在sytem用户下执行。采用PL/SQL做的客户端登陆。

2.项目环境

安装oracle10g，然后再安装PL/SQL，然后用管理员用户system登陆，

打开SQL Window输入图中的SQL语句，操作环境详细的信息如下：

3.环境准备

（1）新建一个表，命令为test，建表语句如下：

test0916

1 create table system.test0916 2 ( 3 seq NUMBER(2) PRIMARY KEY, 4 seqtime DATE 5 )

在SQL窗口执行以上语句；

（2）新建一个存储过程，命名为test_proc，存储过程的语句如下：

test_proc

1 CREATE OR REPLACE PROCEDURE test_proc IS 2 BEGIN 3 INSERT INTO SYSTEM.TEST0916(SEQ, SEQTIME) VALUES(NVL((SELECT MAX(SEQ) FROM SYSTEM.TEST0916) + 1, 0), SYSDATE); 4 COMMIT; 5 EXCEPTION 6 WHEN OTHERS THEN 7 DBMS_OUTPUT.PUT_LINE('Exception happened,data was rollback'); 8 ROLLBACK; 9 END;

在SQL Window窗口中执行以上存储过程，执行完成以后，查看Objects下的Procedures菜单，可以看到新建的存储过程如图：

TEST_PROC前面的图标没有红色的标记，表示存储过程正常；

（3）检查oracle的JOB运行环境

我这里遇到的只是要检查一下oracle的JOB队列的进程数，如果为0表示不运行JOB，

虽然网上说一般默认为10，但是我安装oracle以后就不知道为什么是0。

查看进程数：

修改进程数(如果你的是0，才需要按照以下语句修改)：

我这里已经改为10，假如我要改为11来模拟以下这个操作；

在PL/SQL的command窗口中输入以下语句：

修改进程数

alter system set job_queue_processes=10 scope=both;

运行截图如下：

修改完以后我们这查看一下：

看到了吧，我这是是改为11了吧。

补充说明：

网上有的说修改这个进程数可以修改oracle初始文件，最开始我是找到这个初始文件的位置，然后用notepad++打开后去修改，

保存以后我重启oracle服务和电脑，但是发现此时用PL/SQL登陆oracle的时候，一直报无法识别指定的字符串，这里要说明这样操作不行。

这里有个问题，我们要如何找到oracle初始文件的位置？可以在PL/SQL的command window窗口中执行以下语句看到：

我当时用notepad++修改的是下图阴影部分的内容：

*.job_queue_processes=11

4.定制定时执行任务

假设我们要每隔两分钟向表test0916中插入一条记录，这里我们只需要每隔2分钟执行一次存储过程test_proc即可。

在PL/SQL的command window输入以下语句：

job

1 var job_num number; 2 begin 3 dbms_job.submit(:job_num,'test_proc;',SYSDATE,'sysdate+2/24/60'); 4 end;

执行过程的截图如下：

执行完成以后记得点击PL/SQL的提交按钮；

5.查看执行结果

(1)查看我们新增的JOB是否成功

通过查看DBA_JOBS表中的WHAT字段可以看到最后一条记录即使我们刚新建的定时任务；

(2)查看test0916表是否插入了数据

6.其他相关说明

(1)如何删除一个JOB？

在PL/SQL的command window执行以下语句：

exec dbms_job.remove(81);

说明：这里的数字81是对应DBA_JOBS表中当前要删除的JOB记录所在行的JOB字段的值；

(2)submit的各个参数是什么意思？

使用Submit()过程，工作被正常地计划好。 这个过程有五个参数：job、what、next_date、interval与no_parse。 PROCEDURE Submit ( job OUT binary_ineger, What IN varchar2, next_date IN date, interval IN varchar2, no_parse IN booean:=FALSE)

job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。 前面有一个双引号，在SQL windwo中没有显示，拷贝到command窗口就可以显示出来了； what参数是将被执行的PL/SQL代码块。这里是存储过程的名称，后面有一个英文状态下的分号不能掉了； next_date参数指识何时将运行这个工作。

interval参数何时这个工作将被重执行。 这里加号、括号都必须在英文状态下输入； no_parse参数指示此工作在提交时或执行时是否应进行语法分析——TRUE 指示此PL/SQL代码在它第一次执行时应进行语法分析， 而FALSE指示本PL/SQL代码应立即进行语法分析。

(3)定时执行的时间间隔是怎么设置的？

这里大家可以去百度，网上搜索一般都能找到满足你需求的，主要是结果TRUNC函数来实现。

如何查看并删除oracle的所有定时任务

主要是管理Jobs物件(Jobs页签)

JOB分类有dba_jobs

,

all_jobs

,

user_jobs

,

dba_jobs_running

查询所有JOB

SELECT

JOB,

NEXT_DATE,

NEXT_SEC,

FAILURES,

BROKEN

FROM

ALL_JOBS;

或

SELECT

*

FROM

ALL_JOBS;

删除JOB

(一个一个删,我不记得有没有一次全删的命令)

begin

dbms_job.remove(:job);

--:job可以用dba_jobs.job的值代替如:1198

end;

怎么查看oracle定时文件是否已运行？

你提供的信息太少，我估计你说的是oracle的定时处理任务的机制。查看任务队列，如果要查看当前用户的定时任务队列，可以通过查询user_jobs视图或dba_jobs.

select last_date from user_jobs;可以查看所有任务上次成功执行的时间。

网站栏目：如何查看oracle定时 如何查看oracle定时任务
网站路径：http://kswjz.com/article/hpjphe.html