Oracle 序列号通过定时任务重置

--创建序列号
create sequence seq_settlemanage_inc
minvalue 1 maxvalue 99999
start with 1
increment by 1
nocache
cycle;

select seq_settlemanage_inc.nextval from dual;--获取下一序列值

/*新建存储过程:每天序列0时从1起始*/

CREATE OR REPLACE
procedure reset_seq_settlemanage_inc AS
n NUMBER(10);
tsql VARCHAR2(100);
begin
EXECUTE IMMEDIATE 'select seq_settlemanage_inc.nextval from dual'
INTO n;
n := 99999-n;
tsql := 'alter sequence seq_settlemanage_inc increment by ' || n;
EXECUTE IMMEDIATE tsql;
EXECUTE IMMEDIATE 'select seq_settlemanage_inc.nextval from dual'
INTO n;
tsql := 'alter sequence seq_settlemanage_inc increment by 1';
EXECUTE IMMEDIATE tsql;
end reset_seq_settlemanage_inc;

--创建定时任务
declare
job number;
BEGIN
DBMS_JOB.SUBMIT(
JOB => job, /*自动生成JOB_ID*/
WHAT => 'reset_seq_settlemanage_inc;', /*需要执行的存储过程名称或SQL语句*/
NEXT_DATE => sysdate+3/(24*60), /*初次执行时间-下一个3分钟*/
INTERVAL => 'trunc(sysdate,''mi'')+2/(24*60)' /*每隔1分钟执行一次*/
);
commit;
end;

--查询定时任务
select * from user_jobs where what ='reset_seq_settlemanage_inc;';

--执行定时任务
begin
dbms_job.run(job_id);
commit;
end;
--查看value是否等于0，如果等于0，任务不会自动执行，需把值job_queue_processes调大（0-1000）
select value from v$parameter where name like '%job_queue_processes%'

alter system set job_queue_processes =20;