Oracle, Rename Sequence

---

Oracle, Rename Sequence 

업무 요건 : 시퀀스 RENAME 되나요 ? 채번은요 ?
결론 : 시퀀스 Rename 지원 되구요, RENAME 시점에 CACHE 만큼 SEQUENCE 값이 증가 합니다.
     EX)  현재 LAST_NUMBER 가 100021 이고, CACHE 20 이면

          , RENAME 시점에 LAST_NUMBER 가 100041 로 변경됨 
 
RENAME : Use the RENAME statement to rename a table, view, sequence, or private synonym.
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/RENAME.html#GUID-573347CE-3EB8-42E5-B4D5-EF71CA06FAFC

2022.04.15 - [Oracle/Oh Oracle ] - Oracle Sequence 초기화 : alter sequence 시퀀스명 restart

-- 테스트 시퀀스 생성 : TG_SEQ_OLD
SQL> CREATE SEQUENCE TG_SEQ_OLD
     START WITH 100001
     INCREMENT BY 1
     MINVALUE 1
     MAXVALUE 999999
     CACHE 20
     ORDER;  
Sequence created.

-- 테스트 테이블 : 시퀀스 사용해서 INSERT 
SQL> CREATE TABLE CHECK_SEQ_TABLE ( A1 NUMBER, A2 NUMBER, A3 DATE );
Table created.

SQL> set linesize 180
SQL> COL MIN_VALUE FOR 9999999
SQL> COL INCREMENT_BY FOR 9999999
SQL> COL CACHE_SIZE FOR 9999999
SQL> COL LAST_NUMBER FOR 9999999
SQL> COL SEQUENCE_NAME for a14
SQL> SELECT SEQUENCE_NAME, MIN_VALUE, INCREMENT_BY,CACHE_SIZE, LAST_NUMBER
     FROM DBA_SEQUENCES WHERE SEQUENCE_NAME ='TG_SEQ_OLD'; 

SEQUENCE_NAME  MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
-------------- --------- ------------ ---------- -----------
TG_SEQ_OLD             1            1         20      100001


SQL> DECLARE
     NUM NUMBER := 1 ;
     BEGIN
     WHILE ( NUM < 11 )
     LOOP
     INSERT INTO CHECK_SEQ_TABLE VALUES  ( NUM, TG_SEQ_OLD.NEXTVAL, SYSDATE )  ;
     NUM := NUM+1 ;
     END LOOP ;
     COMMIT ;
     END ;
     /
PL/SQL procedure successfully completed.

SQL> SELECT MIN(A1), MAX(A1), MIN(A2), MAX(A2) FROM CHECK_SEQ_TABLE ;

   MIN(A1)    MAX(A1)    MIN(A2)    MAX(A2)
---------- ---------- ---------- ----------
         1         10     100001     100010  
-- 10개 채번 및 INSERT 완료 

SQL> SELECT  TG_SEQ_OLD.NEXTVAL FROM DUAL ;

   NEXTVAL
----------
    100011   
--- 현재 채번 100011, 아직 9개의 채번이 남은 상태로 확인 

SQL> SELECT SEQUENCE_NAME, MIN_VALUE, INCREMENT_BY,CACHE_SIZE, LAST_NUMBER
     FROM DBA_SEQUENCES WHERE SEQUENCE_NAME ='TG_SEQ_OLD';  

SEQUENCE_NAME  MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
-------------- --------- ------------ ---------- -----------
TG_SEQ_OLD             1            1         20      100021

SQL> RENAME TG_SEQ_OLD TO TG_SEQ_NEW ;

Table renamed.   
-- Table renamed 이지만, 실제로는 시퀀스 rename 이다. !!

SQL> SELECT  TG_SEQ_NEW.NEXTVAL FROM DUAL ;

   NEXTVAL
----------
    100021   
--- 채번 100011 에서  100021 점프됨 
--- // (추정) RENAME 은 DDL 로, RENAME 시점에 CACHE SIZE 만큼 업데이트 될것으로 보임 

SQL> SELECT SEQUENCE_NAME, MIN_VALUE, INCREMENT_BY,CACHE_SIZE, LAST_NUMBER
     FROM DBA_SEQUENCES WHERE SEQUENCE_NAME ='TG_SEQ_NEW'    ;

SEQUENCE_NAME  MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
-------------- --------- ------------ ---------- -----------
TG_SEQ_NEW             1            1         20      100041  
-- 역시나 LAST_NUMBER 가 100021 >> 100041 변경