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起始*/
--v_maxNum序列最大值
--v_sequenceName序列名称

create or replace procedure reset_seq_settlemanage_inc(v_maxNum IN NUMBER,
v_sequenceName IN VARCHAR2) AS
n NUMBER(10);
tsql VARCHAR2(100);
begin
EXECUTE IMMEDIATE 'select '||v_sequenceName||'.nextval from dual'
INTO n;
n := v_maxNum-n;
tsql := 'alter sequence '||v_sequenceName||' increment by ' || n;
EXECUTE IMMEDIATE tsql;
EXECUTE IMMEDIATE 'select '||v_sequenceName||'.nextval from dual'
INTO n;
tsql := 'alter sequence '||v_sequenceName||' 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(99999,''seq_settlemanage_inc'');', /*需要执行的存储过程名称或SQL语句*/
NEXT_DATE => sysdate+1/(24*60), /*初次执行时间-下一个1分钟*/
INTERVAL => 'trunc(sysdate,''mi'')+1/(24*60)' /*每隔1分钟执行一次*/
);
commit;
end;

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

--查询定时任务
select * from user_jobs where what like '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;