定时清空序列，从1开始

1、添加序列

CREATE SEQUENCE "CONVERGENCE"."SEQ_ERROR"
 INCREMENT BY 1
 MINVALUE 0
 MAXVALUE 9999999999999999999999999999
 START WITH 1
 CACHE 10;

2、创建存储过程

create or replace procedure seq_reset(v_seqname varchar2) as n number(10);
tsql varchar2(100);
 begin
 execute immediate 'select '||v_seqname||'.nextval from dual' into n;
  n:=-(n);
  tsql:='alter sequence '||v_seqname||' increment by '|| n;
  execute immediate tsql;
 execute immediate 'select '||v_seqname||'.nextval from dual' into n;
  tsql:='alter sequence '||v_seqname||' increment by 1';
 execute immediate tsql;
 end seq_reset;

3、添加定时任务

declare job_n number;
begin
  dbms_job.submit(job_n ,'seq_reset(SEQ_ERROR);',sysdate,'TRUNC(sysdate+1)');
  commit;
end;

4、相关语句

查看所有jobs

select * from dba_jobs

查看下一个序列

SELECT SEQ_ERROR.nextval from dual;

调用存储过程

call seq_reset();