Oracle 11g
项目上需要每天从1开始的流水号,用JAVA来启动定时任务每天重置内存中保存的流水号容易丢失,在数据库中维护maxnum每次生成完流水号还得update,恰巧项目需要为亿级数据生成...想到了用序列+job解决这个问题。
创建序列:
--注意,若想达到重置后第一次取nextval为1,则创建序列时最小值应设置0
create sequence SEQ_LSH
minvalue 0
maxvalue 9999999999
start with 1
increment by 1
cache 10;
创建存储过程:
CREATE OR REPLACE PROCEDURE INIT_LSH_SEQUENCE Authid Current_User AS
n number(10);
tsql varchar2(100);
BEGIN
with 1 increment by 1 cache 10';
execute immediate 'select SEQ_LSH.nextval from dual' into n;
n := -(n);
tsql:='alter sequence SEQ_LSH increment by ' || n;
execute immediate tsql;
execute immediate 'select SEQ_LSH.nextval from dual' into n;
tsql:='alter sequence SEQ_LSH increment by 1';
execute immediate tsql;
END;
逻辑:通过将序列步长改为-(nextval),取一次nextval,以此将序列值归零,然后将步长改为正增长1
Job:
--提交job
declare jobno pls_integer;
begin
dbms_job.submit(jobno,'INIT_LSH_SEQUENCE;', sysdate, 'TRUNC(sysdate) + 1 +1/(24)');
commit;
end;
/
参数1:job号,提交job后自动生成
参数2:sql语句或存储过程名称,记得带英文分号“;”
参数3:初次执行时间
参数4:间隔多久执行
※
job的运行频率设置
1.每天固定时间运行,比如早上8:10分钟:Trunc(Sysdate+1) + (8*60+10)/24*60
2.Toad中提供的:
每天:trunc(sysdate+1)
每周:trunc(sysdate+7)
每月:trunc(sysdate+30)
每个星期日:next_day(trunc(sysdate),'星期日')
每天6点:trunc(sysdate+1)+6/24
半个小时:sysdate+30/(24*60)
3.每个小时的第15分钟运行,比如:8:15,9:15,10:15…:trunc(sysdate,'hh')+(60+15)/(24*60) 。
--查看job运行状态
select job, next_date, next_sec, failures, broken from user_jobs;
--关闭
begin
dbms_job.broken(185, true, sysdate);
commit;
end;
/
--启动
begin
dbms_job.broken(185, false, sysdate);
commit;
end;
/
--删除job
BEGIN
dbms_job.remove (185);
commit;
END;
/
--运行job
begin
dbms_job.run(185);
commit;
end;
/
起初是想通过job+存储过程(内部drop掉序列,重新create序列),但是遇到了权限问题,在本地运行存储过程正常,通过job执行存储过程出错ORA-12011。需要显式授权“grant create sequence to sino;” 。我权限不足,没想麻烦DBA,就用这种不需要删除重建的方式重置序列。
参考: