| 修改SEQUENCE的LAST_NUMBER | DBA之路：OoNiceDream |

修改SEQUENCE的LAST_NUMBER

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/03/sequence_last_number.html

应用人员提出要修改SEQUENCE的LAST_NUMBER，增加50W，其间的序列值用于新增数据使用。
翻开手册发现没有直接修改LAST_NUMBER的语句，倒是在PL/SQL中可以直接修改。
这里以LAST_NUMBER增加10000为例，修改过程如下：

SQL>select * from dba_sequences where sequence_name='SQ_TEST';
 
SEQUENCE_OWNER       SEQUENCE_NAME         MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
-------------------- -------------------- ---------- ---------- ------------ - - ---------- -----------
SYS                  SQ_TEST                       1 1.0000E+27            1 N N         20       10041
 
SQL>select SQ_TEST.currval from dual;
 
   CURRVAL
----------
     10024
 
SQL>select * from dba_sequences where sequence_name='SQ_TEST';
 
SEQUENCE_OWNER       SEQUENCE_NAME         MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
-------------------- -------------------- ---------- ---------- ------------ - - ---------- -----------
SYS                  SQ_TEST                       1 1.0000E+27            1 N N         20       10041
 
SQL>alter sequence SQ_TEST increment by 10000 nocache;
 
序列已更改。
 
SQL>select SQ_TEST.nextval from dual;
 
   NEXTVAL
----------
     20024
 
SQL>alter sequence SQ_TEST increment by 1 nocache;
 
序列已更改。
 
SQL>select * from dba_sequences where sequence_name='SQ_TEST';
 
SEQUENCE_OWNER       SEQUENCE_NAME         MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
-------------------- -------------------- ---------- ---------- ------------ - - ---------- -----------
SYS                  SQ_TEST                       1 1.0000E+27            1 N N          0       20025
 
SQL>declare
  2    LastValue integer;
  3  begin
  4    loop
  5      select SQ_TEST.currval into LastValue from dual;
  6      exit when LastValue >= 20041 - 1;
  7      select SQ_TEST.nextval into LastValue from dual;
  8    end loop;
  9  end;
 10  /
 
PL/SQL 过程已成功完成。
 
SQL>select * from dba_sequences where sequence_name='SQ_TEST';
 
SEQUENCE_OWNER       SEQUENCE_NAME         MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
-------------------- -------------------- ---------- ---------- ------------ - - ---------- -----------
SYS                  SQ_TEST                       1 1.0000E+27            1 N N          0       20041
 
SQL>alter sequence SQ_TEST increment by 1 cache 20;
 
序列已更改。
 
SQL>

从PL/SQL中s查看到的语句如下：

-- Modify the last number 
alter sequence SQ_TEST increment by 10000 nocache;
select SQ_TEST.nextval from dual;
alter sequence SQ_TEST increment by 1 nocache;
declare
  LastValue integer;
begin
  loop
    select SQ_TEST.currval into LastValue from dual;
    exit when LastValue >= 20041 - 1;
    select SQ_TEST.nextval into LastValue from dual;
  end loop;
end;
/
alter sequence SQ_TEST increment by 1 cache 20;