不知道问题出在哪里,请各位帮忙看看吧create table G_TEST
(
G_ID NUMBER(12),
G_DATE DATE
);--创建表G_TESTcreate sequence G_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 141
increment by 1
cache 20; --创建自增序列create or replace procedure prc_g_test is
begin
insert into g_test values(g_seq.nextval,sysdate);
end prc_g_test; --创建存储过程,执行插入记录,包含序列号和日期declare jobN number;--系统指定job编号?
begin
sys.dbms_job.submit(jobN,'prc_g_test;',sysdate,'sysdate+30/1440');--每30分钟执行一次
end;--创建job,提交job编号、执行的存储过程、当前时间?、下一次执行的时间(计时,秒)
begin
dbms_job.run(242);--dbms_job.run(:job);
end; --立即运行job
执行最后一句的时候马上就插入了记录,但是等了一中午再看却没有新的记录插入。
查看user_jobs,break标志为N,那就是正在运行吧
dba_jobs_running也显示有这个job的初学,拜托各位了!
(
G_ID NUMBER(12),
G_DATE DATE
);--创建表G_TESTcreate sequence G_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 141
increment by 1
cache 20; --创建自增序列create or replace procedure prc_g_test is
begin
insert into g_test values(g_seq.nextval,sysdate);
end prc_g_test; --创建存储过程,执行插入记录,包含序列号和日期declare jobN number;--系统指定job编号?
begin
sys.dbms_job.submit(jobN,'prc_g_test;',sysdate,'sysdate+30/1440');--每30分钟执行一次
end;--创建job,提交job编号、执行的存储过程、当前时间?、下一次执行的时间(计时,秒)
begin
dbms_job.run(242);--dbms_job.run(:job);
end; --立即运行job
执行最后一句的时候马上就插入了记录,但是等了一中午再看却没有新的记录插入。
查看user_jobs,break标志为N,那就是正在运行吧
dba_jobs_running也显示有这个job的初学,拜托各位了!
dba_jobs_running里面没有在运行的job
会不会是job_queue_processes为0了?
怎么在PLSQL里面查看job_queue_processes呢?
begin
insert into g_test values(g_seq.nextval,sysdate);
commit;
end prc_g_test; --创建存储过程,执行插入记录,包含序列号和日期
2 (
3 G_ID NUMBER(12),
4 G_DATE DATE
5 );
create public synonym G_TEST for scott.G_TEST表已创建。scott@SZTYORA> create sequence G_SEQ
2 minvalue 1
3 maxvalue 999999999999999999999999999
4 start with 141
5 increment by 1
6 cache 20;序列已创建。scott@SZTYORA> create or replace procedure prc_g_test is
2 begin
3 insert into g_test values(g_seq.nextval,sysdate);
4 commit;
5 end prc_g_test;
6 /过程已创建。scott@SZTYORA> declare job_prc_g_test number;
2 begin
3 sys.dbms_job.submit(job_prc_g_test,'prc_g_test;',sysdate,'sysdate+30/1440');
4 end;
5 /PL/SQL 过程已成功完成。scott@SZTYORA> col what for a40
scott@SZTYORA> select job, what from user_jobs; JOB WHAT
---------- ----------------------------------------
41 prc_g_test;scott@SZTYORA> begin
2 dbms_job.run(41);
3 end;
4 /PL/SQL 过程已成功完成。scott@SZTYORA> select * from g_test; G_ID G_DATE
---------- -------------------
141 2011-02-18 13:14:12
scott@SZTYORA> begin
2 dbms_job.remove(41);
3 end;
4 /PL/SQL 过程已成功完成。
CREATE TABLE test2(
row_no NUMBER(18,0),
texts VARCHAR2(30),
cdate DATE DEFAULT SYSDATE);CREATE SEQUENCE test2_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE;CREATE OR REPLACE TRIGGER test2_trig_autoinc
BEFORE INSERT ON test2
FOR EACH ROW
BEGIN
IF (:new.row_no IS NULL) THEN
-- SELECT test2_seq.nextval INTO :new.row_no FROM DUAL; -- Oracle 10g
:new.row_no := test2_seq.nextval; -- Oracle 11g
END IF;
END;
/
CREATE OR REPLACE PROCEDURE test2_in_proc
AS
v_row_no NUMBER(18,0);
v_texts VARCHAR2(30);
BEGIN
v_row_no := test2_seq.nextval;
v_texts := 'luoyoumou '||v_row_no; INSERT INTO test2(row_no, texts) VALUES(v_row_no, v_texts);
-- DBMS_OUTPUT.PUT_LINE('luoyoumou');
COMMIT;
END;
/EXECUTE test2_in_proc;
SELECT * FROM test2;column texts for a20
SELECT row_no, texts, to_char(cdate,'YYYY-MM-DD HH24:MI:SS') cdate
FROM test2
order by cdate;
ROW_NO TEXTS CDATE
---------- ------------------------------------------------------------ -------------------------------------
6 luoyoumou 6 2010-05-17 11:59:36
1 luoyoumou 1 2010-05-17 11:53:34
2 luoyoumou 2 2010-05-17 11:53:47
3 luoyoumou 3 2010-05-17 11:53:48
4 luoyoumou 4 2010-05-17 11:53:48
5 luoyoumou 5 2010-05-17 11:53:49
11 luoyoumou 11 2010-05-17 12:04:36
7 luoyoumou 7 2010-05-17 12:00:36
8 luoyoumou 8 2010-05-17 12:01:36
9 luoyoumou 9 2010-05-17 12:02:36
10 luoyoumou 10 2010-05-17 12:03:36variable test2_in_proc_jb number;begin
dbms_job.submit(:test2_in_proc_jb,'test2_in_proc;',sysdate,'sysdate+1/1440');
end;
/begin
dbms_job.remove(440);
end;
/
scott@SZTYORA> select job, what from user_jobs; JOB WHAT
---------- ------------------------------------------------
69 BEGIN
DELETE t WHERE n1 BE
TWEEN 6000 AND 7000;
dbms_lock.sleep(5);
COMMIT;
END; 112 del_order_id_seq_proc;
113 del_order_id_seq_proc;
114 del_order_id_seq_proc;
115 del_order_id_seq_proc;
116 del_order_id_seq_proc;
117 del_order_id_seq_proc;
118 del_order_id_seq_proc;
119 del_order_id_seq_proc;
120 del_order_id_seq_proc;
200 refresh_mv_proc;
201 refresh_mv_proc;
880 dbms_refresh.refresh('"SCOTT"."CQHKPAYPID"');
881 dbms_refresh.refresh('"SCOTT"."CQHKPAYMSG"');
4308 dbms_refresh.refresh('"SCOTT"."FREEORDERUSER"');
878 dbms_refresh.refresh('"SCOTT"."CQHKPAYDES"');
4273 test2_in_proc;
4270 test2_in_proc;
4307 dbms_refresh.refresh('"SCOTT"."CITY"');------------------------------- 新建 Job -------------------------------------------------------------------
variable job_online_tj2 number;
begin
dbms_job.submit(:job_online_tj2,'online_tj_proc2;',sysdate,'sysdate+1/4');
end;
/begin
dbms_job.run(52);
end;
/
------------------------------- 查看 Job -------------------------------------------------------------------lftest@SZTYORA> select job, what from user_jobs; JOB WHAT
---------- --------------------------------------------------
46 online_tj_proc2;
47 online_tj_proc2;
48 online_tj_proc2;
49 online_tj_proc2;
50 online_tj_proc2;
51 online_tj_proc2;
52 online_tj_proc2;已选择7行。------------------------------- 删除 Job -------------------------------------------------------------------
begin
dbms_job.remove(46);
end;
/begin
dbms_job.remove(47);
end;
/begin
dbms_job.remove(48);
end;
/begin
dbms_job.remove(49);
end;
/begin
dbms_job.remove(50);
end;
/begin
dbms_job.remove(51);
end;
/