创建了一个JOBS对序列每天进行删除重建 也许几天之内执行没有问题,几天后突然就失效了。
BEGINJOBS语句就变成这样了。下面的是执行的存储过程
SYS.DBMS_JOB.REMOVE(141);
COMMIT;
END;
/
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'ClearSeq;'
,next_date => to_date('01/01/4000 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE + 1)'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
SYS.DBMS_JOB.BROKEN
(job => X,
broken => TRUE);
COMMIT;
END;
/
CREATE OR REPLACE procedure WZFCARC.ClearSeq as求大神指导什么问题,怎么解决。谢谢
n_count number(8);
begin
select count(1)
into n_count
from user_sequences t
where t.sequence_name = 'DA_JBQ';
if n_count > 0 then
execute immediate 'DROP SEQUENCE WZFCARC.DA_JBQ';
execute immediate 'CREATE SEQUENCE DA_JBQ
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 0
NOCYCLE
NOCACHE
NOORDER';
end if;
execute immediate 'CREATE SEQUENCE DA_JBQ
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 0
NOCYCLE
NOCACHE
NOORDER';
commit;
end;
/
------解决方案--------------------
每天重建sequence并不是太好,因为这会导致依赖它的Oracle程序失效。可以尝试写成这样
DECLARE
v_number NUMBER;
BEGIN
SELECT DA_JBQ.nextval INTO v_number from dual;
EXECUTE IMMEDIATE 'alter sequence DA_JBQ increment by -'
------解决方案--------------------
v_number
------解决方案--------------------
' minvalue 0';
SELECT DA_JBQ.nextval INTO v_number from dual;
EXECUTE IMMEDIATE 'alter sequence DA_JBQ increment by 1';
END;
另外需要检查是否因为drop过程中有其他对象调用该seq导致job失败