大家好,我想让名为A的存储过程,在0:30 1:30、2:30 、3:30 、、4:30 、5:30 、6:30 ......这些以每隔1小时间隔运行一次,且时间点刚好在30上运行。请问我该怎样实现?每次重新运行JOB时,也希望在同样的点上执行?请问该JOB要怎样写?
解决方案 »
- oracle 关于月中的周 的sql 语句! 急求!
- Oracle 怎么两表间复制数据,表结构相同。
- 项目频繁操作oracle10g,导致读写oracle很慢!!!
- 请教如何写这个SQL条件
- oracle PL/SQL 用什么开发工具较好呢?
- 求助各位高手一个关于vc调用oracle存储过程的问题!
- 能用rman备份建新库吗
- 表a有字段'学生id','科目','分数',怎么查出结果集如:'学生id','科目1分数','科目2分数'..'科目n分数'
- 存储过程编译出错,请大家帮忙看一下!
- 小弟想知道Oracle与Sql Server各有什么优缺点,学了sqlserver有没有必要再学Oracle呢
- oracle11RAC+VM安装centos5.6时,最后运行root.sh时报错Failed to update the profile with the n
- oracle10g的Dblink的问题ORA-02020
15:19:35 2 id NUMBER(38,0),
15:19:35 3 uname varchar2(30)
15:19:35 4 );表已创建。15:19:35 SCOTT@tdwora >
15:19:35 SCOTT@tdwora > CREATE OR REPLACE PROCEDURE pro_test
15:19:35 2 IS
15:19:35 3 BEGIN
15:19:35 4 INSERT INTO test(id,uname)
15:19:35 5 SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24') as id,
15:19:35 6 'LYM-'||TO_CHAR(SYSDATE,'YYYYMMDDHH24') as uname
15:19:35 7 FROM DUAL;
15:19:35 8 COMMIT;
15:19:35 9 END;
15:19:35 10 /SP2-0804: 过程已创建, 但带有编译警告15:19:35 SCOTT@tdwora >
15:19:35 SCOTT@tdwora > exec pro_test;PL/SQL 过程已成功完成。15:19:35 SCOTT@tdwora >
15:19:35 SCOTT@tdwora > select * from test; ID UNAME
---------------- ------------------------------------------------------------
2012101115 LYM-201210111515:19:36 SCOTT@tdwora > variable job_pro_test number;
15:19:47 SCOTT@tdwora >
15:19:47 SCOTT@tdwora > begin
15:19:47 2 dbms_job.submit(:job_pro_test,'pro_test;',TRUNC(SYSDATE,'HH'),'TRUNC(SYSDATE,''HH'')+1/48');
15:19:47 3 end;
15:19:47 4 /PL/SQL 过程已成功完成。
15:20:07 SCOTT@tdwora > col what for a20
15:22:43 SCOTT@tdwora > alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';会话已更改。15:23:09 SCOTT@tdwora > SELECT job, last_date, this_date, next_date, what from user_jobs; JOB LAST_DATE THIS_DATE NEXT_DATE WHAT
---------- ------------------- ------------------- ------------------- --------------------
23 2012-10-11 15:22:38 2012-10-11 15:30:00 pro_test;
15:34:46 SCOTT@tdwora > CREATE TABLE test(
15:34:46 2 id NUMBER(38,0),
15:34:46 3 uname varchar2(30)
15:34:46 4 );表已创建。15:34:46 SCOTT@tdwora >
15:34:46 SCOTT@tdwora > CREATE OR REPLACE PROCEDURE pro_test
15:34:46 2 IS
15:34:46 3 BEGIN
15:34:46 4 INSERT INTO test(id,uname)
15:34:46 5 SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MI') as id,
15:34:46 6 'LYM-'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MI') as uname
15:34:46 7 FROM DUAL;
15:34:46 8 COMMIT;
15:34:46 9 END;
15:34:46 10 /SP2-0804: 过程已创建, 但带有编译警告15:34:46 SCOTT@tdwora >
15:34:46 SCOTT@tdwora > exec pro_test;PL/SQL 过程已成功完成。15:34:46 SCOTT@tdwora >
15:34:46 SCOTT@tdwora > select * from test; ID UNAME
---------------- ------------------------------------------------------------
201210111534 LYM-20121011153415:34:46 SCOTT@tdwora >
15:34:46 SCOTT@tdwora > variable job_pro_test number;
15:34:46 SCOTT@tdwora >
15:34:46 SCOTT@tdwora > begin
15:34:46 2 dbms_job.submit(:job_pro_test,'pro_test;',TRUNC(SYSDATE,'HH24')+1/48,'TRUNC(SYSDATE,''HH24'')+1/48+1/24');
15:34:46 3 end;
15:34:46 4 /PL/SQL 过程已成功完成。15:34:46 SCOTT@tdwora >
15:34:46 SCOTT@tdwora > col what for a20
15:34:46 SCOTT@tdwora > alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';会话已更改。15:34:46 SCOTT@tdwora > select job, what from user_jobs; JOB WHAT
---------- --------------------
25 pro_test;15:34:46 SCOTT@tdwora > SELECT job, last_date, this_date, next_date, what from user_jobs; JOB LAST_DATE THIS_DATE NEXT_DATE WHAT
---------- ------------------- ------------------- ------------------- --------------------
25 2012-10-11 15:30:00 pro_test;15:35:38 SCOTT@tdwora > begin
15:36:39 2 dbms_job.RUN(25);
15:36:39 3 end;
15:36:39 4 /PL/SQL 过程已成功完成。15:36:40 SCOTT@tdwora > SELECT job, last_date, this_date, next_date, what from user_jobs; JOB LAST_DATE THIS_DATE NEXT_DATE WHAT
---------- ------------------- ------------------- ------------------- --------------------
25 2012-10-11 15:36:40 2012-10-11 16:30:00 pro_test;
id NUMBER(38,0),
uname varchar2(30)
);CREATE OR REPLACE PROCEDURE pro_test
IS
BEGIN
INSERT INTO test(id,uname)
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MI') as id,
'LYM-'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MI') as uname
FROM DUAL;
COMMIT;
END;
/exec pro_test;select * from test;variable job_pro_test number;begin
dbms_job.submit(:job_pro_test,'pro_test;',TRUNC(SYSDATE,'HH24')+1/48,'TRUNC(SYSDATE,''HH24'')+1/48+1/24');
end;
/col what for a20
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
select job, what from user_jobs;
SELECT job, last_date, this_date, next_date, what from user_jobs;-- 手动运行一个Job
begin
dbms_job.RUN(25);
end;
/ -- 删除一个Job
begin
dbms_job.remove(25);
end;
/