ORACLE jobs 执行问题，JOBS执行一段时间后失效。

[z273894270]

创建了一个JOBS对序列每天进行删除重建 也许几天之内执行没有问题，几天后突然就失效了。

BEGIN 
  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;
/

JOBS语句就变成这样了。下面的是执行的存储过程

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;
/

求大神指导什么问题，怎么解决。谢谢

[z273894270]

自己顶一下 不要沉了。

[shiyiwan]

每天重建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失败