oracle定时任务重置序列

1、创建序列

2、创建存储过程

create or replace procedure SEQ_A_ID_RESET(v_seqname varchar2) as 
       n number(10);
       tsql varchar2(100);
 begin
 execute immediate 'select '||v_seqname||'.nextval from dual' into n;
  n:=-(n-1);
  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_A_ID_RESET;

3、创建DBMS_JOB

begin
  sys.dbms_job.change(job => 3,
                      what => 'SEQ_A_ID_RESET(''SEQ_A_ID'');',
                      next_date => to_date('18-08-2021 22:43:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'TRUNC(sysdate,''mi'') + 1/ (24*60)');　　--每天执行
　　　　　　　　　　　--  interval => 'ADD_MONTHS(trunc(sysdate,''yyyy''),12)+1/24');　 --每年执行

commit; end; /