Oracle Sequence 초기화 : alter sequence 시퀀스명 restart

 

alter sequence 시퀀스명 restart

매뉴얼상으로는 "To restart the sequence at a different number, you must drop and re-create it."

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-SEQUENCE.html#GUID-A6468B63-E7C9-4EF0-B048-82FE2449B26D

 

https://rogertroller.com/2018/02/20/oracle-18c-alter-sequence-restart/

>>> I’m using 12.1 and the RESTART option worked as well. I hope Oracle documents it soon.

--  Syntax
-- 원래 설정된 값으로 재시작
ALTER SEQUENCE MYSEQ RESTART
-- 사용자가 지정한 값으로 재시작 
ALTER SEQUENCE MYSEQ RESTART START WITH 1500 ;

 

CASE 1 )

CREATE SEQUENCE MonthlySeq
   START WITH 1
   INCREMENT BY 1
   MINVALUE 1
   MAXVALUE 9999;

DECLARE
   l_val NUMBER;
BEGIN
   FOR i IN 1..500
   LOOP
      l_val := MonthlySeq.NEXTVAL;
   END LOOP;
END;
/

SELECT MonthlySeq.nextval
FROM dual;

NEXTVAL
-------
    501
ALTER SEQUENCE MonthlySeq RESTART;

SELECT MonthlySeq.nextval
FROM dual;

NEXTVAL
-------
      1



CASE 2)
CREATE SEQUENCE MonthlySeq
   START WITH 1
   INCREMENT BY 1
   MINVALUE 1
   MAXVALUE 9999;

DECLARE
   l_val NUMBER;
BEGIN
   FOR i IN 1..500
   LOOP
      l_val := MonthlySeq.NEXTVAL;
   END LOOP;
END;
/

SELECT MonthlySeq.nextval
FROM dual;

NEXTVAL
-------
    501
ALTER SEQUENCE MonthlySeq RESTART START WITH 1500;

SELECT MonthlySeq.nextval
FROM dual;

NEXTVAL
-------
   1500


CASE 3)
CREATE SEQUENCE MonthlySeq
   START WITH 1
   INCREMENT BY 1
   MINVALUE 1
   MAXVALUE 9999;

CREATE TABLE t (x NUMBER);

DECLARE
   l_val NUMBER;
BEGIN
   FOR i IN 1..500
   LOOP
      l_val := MonthlySeq.NEXTVAL;
   END LOOP;
END;
/

SELECT MonthlySeq.nextval
FROM dual;

NEXTVAL
-------
    501

INSERT INTO t VALUES (1);
ALTER SEQUENCE MonthlySeq RESTART START WITH 1500;

ROLLBACK;

SELECT *
FROM t;

X
-
1​

 

+++ 주의 사항

CREATE SEQUENCE TGSEQ 
   START WITH 1
   INCREMENT BY 1
   MINVALUE 1
   MAXVALUE 9999;

SELECT SEQUENCE_NAME, CACHE_SIZE, LAST_NUMBER from dba_sequences where sequence_name ='TGSEQ'

SEQUENCE_NAME                  CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
TGSEQ                          20          1  
   
DECLARE
   l_val NUMBER;
BEGIN
   FOR i IN 1..500
   LOOP
      l_val := TGSEQ.NEXTVAL;
   END LOOP;
END;
/

SELECT SEQUENCE_NAME, CACHE_SIZE, LAST_NUMBER from dba_sequences where sequence_name ='TGSEQ';

SEQUENCE_NAME                  CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
TGSEQ                                  20         501

ALTER SEQUENCE tgseq restart start with 1;

Sequence altered.

SELECT SEQUENCE_NAME, CACHE_SIZE, LAST_NUMBER from dba_sequences where sequence_name ='TGSEQ';

SEQUENCE_NAME                  CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
TGSEQ                                  20           1



SELECT TGSEQ.NEXTVAL FROM DUAL ;

   NEXTVAL
----------
         1

SELECT SEQUENCE_NAME, CACHE_SIZE, LAST_NUMBER from dba_sequences where sequence_name ='TGSEQ';

SEQUENCE_NAME                  CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
TGSEQ                                  20          21

SQL>
--- 채번 하는 순간 CACHE(20)만큼 LAST_NUMBER 가 증가됨 
-- ( 1로 RESET 하고 갑자기 21으로 증가해도 놀라지 말자 ) 
-- 놀랐던 1인이....