修改oracle sequence start with 起始值 sql实例

1.测试sequence 

create sequence test_seq
minvalue 1
maxvalue 999999999999999999999999
start with 1
increment by 1
nocache
cycle;

 

2.修改sequence start with 起始值（将sequence起始值从1改为200）

-- Alter sequence
alter sequence TEST_SEQ
order;
-- Modify the last number
alter sequence TEST_SEQ increment by 199 nocache;
select TEST_SEQ.nextval from dual;
alter sequence TEST_SEQ increment by 1 nocache;
declare
  LastValue integer;
begin
  loop
    select TEST_SEQ.currval into LastValue from dual;
    exit when LastValue >= 200 - 1;
    select TEST_SEQ.nextval into LastValue from dual;
  end loop;
end;
/
alter sequence TEST_SEQ increment by 1 nocache;