我按照网上的例子一步一步的执行:
SQL> create table test_job(time date);
Table created
SQL> create or replace procedure MYPROC as
2 begin
3 insert into test_job values(sysdate);
4 end;
5 /
Procedure created
SQL> variable job1 number;
SQL> begin
2 dbms_job.submit(:job1,'MYPROC;',sysdate,'sysdate+1/1440');
3 end;
4 /
PL/SQL procedure successfully completed
job1
---------
148
SQL> begin
2 dbms_job.run(:job1);
3 end;
4 /
PL/SQL procedure successfully completed
job1
---------
148
SQL> select to_char(time,'yyyy-mm-dd hh24:mi:ss') 时间 from test_job;
时间
---------------------------------------------------------------------------
2010-01-27 09:06:52
SQL> select to_char(time,'yyyy-mm-dd hh24:mi:ss') 时间 from test_job;
时间
---------------------------------------------------------------------------
2010-01-27 09:06:52
隔了好久之后还是一条???查询:
SQL> SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
2 FROM DBA_JOBS;
JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
501 2008-6-2 17 17:31:08 N
502 2008-6-2 17 17:32:04 N
503 2008-6-2 17 17:31:08 N
47 2008-6-15 1 10:50:29 N
48 2008-6-15 1 10:50:30 N
4 2008-6-26 1 16:11:31 N
5 2008-6-26 1 16:11:31 N
108 2008-11-28 08:41:06 4 N
127 2009-2-11 8 08:40:00 N
564 2008-8-7 14 14:47:51 N
565 2008-8-7 23 23:07:01 N
107 2008-11-30 01:00:00 3 N
148 2010-1-27 9 09:07:52 0 N
13 rows selectedSQL> SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC
2 FROM DBA_JOBS_RUNNING r, DBA_JOBS j
3 WHERE r.JOB = j.JOB;
SID JOB LOG_USER THIS_DATE THIS_SEC
---------- ---------- ------------------------------ ----------- ----------------
请大家帮忙看看??谢谢
SQL> create table test_job(time date);
Table created
SQL> create or replace procedure MYPROC as
2 begin
3 insert into test_job values(sysdate);
4 end;
5 /
Procedure created
SQL> variable job1 number;
SQL> begin
2 dbms_job.submit(:job1,'MYPROC;',sysdate,'sysdate+1/1440');
3 end;
4 /
PL/SQL procedure successfully completed
job1
---------
148
SQL> begin
2 dbms_job.run(:job1);
3 end;
4 /
PL/SQL procedure successfully completed
job1
---------
148
SQL> select to_char(time,'yyyy-mm-dd hh24:mi:ss') 时间 from test_job;
时间
---------------------------------------------------------------------------
2010-01-27 09:06:52
SQL> select to_char(time,'yyyy-mm-dd hh24:mi:ss') 时间 from test_job;
时间
---------------------------------------------------------------------------
2010-01-27 09:06:52
隔了好久之后还是一条???查询:
SQL> SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
2 FROM DBA_JOBS;
JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- ----------- ---------------- ---------- ------
501 2008-6-2 17 17:31:08 N
502 2008-6-2 17 17:32:04 N
503 2008-6-2 17 17:31:08 N
47 2008-6-15 1 10:50:29 N
48 2008-6-15 1 10:50:30 N
4 2008-6-26 1 16:11:31 N
5 2008-6-26 1 16:11:31 N
108 2008-11-28 08:41:06 4 N
127 2009-2-11 8 08:40:00 N
564 2008-8-7 14 14:47:51 N
565 2008-8-7 23 23:07:01 N
107 2008-11-30 01:00:00 3 N
148 2010-1-27 9 09:07:52 0 N
13 rows selectedSQL> SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC
2 FROM DBA_JOBS_RUNNING r, DBA_JOBS j
3 WHERE r.JOB = j.JOB;
SID JOB LOG_USER THIS_DATE THIS_SEC
---------- ---------- ------------------------------ ----------- ----------------
请大家帮忙看看??谢谢
2 begin
3 insert into test_job values(sysdate);
4 end;
commit;呢?
create or replace procedure MYPROC as
begin
insert into test_job values(sysdate);
commit;
end;并重新
SQL> begin
2 dbms_job.run(148);
3 end;
4 /SQL> select to_char(time,'yyyy-mm-dd hh24:mi:ss') 时间 from test_job;
时间
---------------------------------------------------------------------------
2010-01-27 09:06:52
2010-01-27 09:25:37就是只有我run的那次执行了MYPROC,之后就不执行了??、
SQL> show parameter job_queue_processesNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10
还有这个任务是每分钟执行一遍,你是否等够了一分钟
你的参数没有设置吧, 设置初始化参数 job_queue_processes
sql> alter system set job_queue_processes=n;(n>0)
job_queue_processes最大值为1000
查看job queue 后台进程
sql>select name,description from v$bgprocess;
i_jobnum number(10);
begin
dbms_job.submit(i_jobnum,
'MYPROC;',
sysdate,
'sysdate+1/1440');
commit;
end;
/ 你把JOB改成这种创建方式
select JOB from user_jobs where what='MYPROC;begin
dbms_job.run(JOB);
end;
/
2 begin
3 insert into test_job values(sysdate);
4 end;
5 /过程已创建。SQL> create or replace procedure MYPROC as
2 begin
3 insert into test_job values(sysdate);
4 commit;
5 end;
6 /过程已创建。SQL> variable job1 number
SQL> begin
2 dbms_job.submit(:job1,'MYPROC;',sysdate,'sysdate+1/1440');
3 end;
4 /PL/SQL 过程已成功完成。SQL> begin
2 dbms_job.run(:job1);
3 end;
4 /PL/SQL 过程已成功完成。SQL> select to_char(time,'yyyy-mm-dd hh24:mi:ss') 时间 from test_job;时间
-------------------
2010-01-27 09:48:23SQL> select to_char(time,'yyyy-mm-dd hh24:mi:ss') 时间 from dual;
select to_char(time,'yyyy-mm-dd hh24:mi:ss') 时间 from dual
*
第 1 行出现错误:
ORA-00904: "TIME": 标识符无效
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 时间 from dual;时间
-------------------
2010-01-27 09:49:36SQL> select to_char(time,'yyyy-mm-dd hh24:mi:ss') 时间 from test_job;时间
-------------------
2010-01-27 09:48:23
2010-01-27 09:49:24
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 时间 from dual;时间
-------------------
2010-01-27 09:50:11SQL> select to_char(time,'yyyy-mm-dd hh24:mi:ss') 时间 from test_job;时间
-------------------
2010-01-27 09:48:23
2010-01-27 09:49:24
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') 时间 from dual;时间
-------------------
2010-01-27 09:50:27SQL> select to_char(time,'yyyy-mm-dd hh24:mi:ss') 时间 from test_job;时间
-------------------
2010-01-27 09:48:23
2010-01-27 09:49:24
2010-01-27 09:50:29SQL>sysdate+1/1440这样的写法会不准确,但只要调度无错,肯定不会出现不执行的情况。
show parameter job看看结果
SQL> show parameter job_queue_processes ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 0