행위

오라클 시퀀스 관리

DB CAFE

thumb_up 추천메뉴 바로가기


1 오라클 시퀀스 관리[편집]

1.1 시퀀스 생성[편집]

create_sequence.gif


  • 다음 구문을 지정하지 않으면 1로 시작하여 MAX값 없이 1 씩 증가하는 오름차순 시퀀스를 생성합니다.
  • INCREMENT BY -1 만 지정하면 -1로 시작하고 MIN값 없이 감소하는 내림차순 시퀀스가 생성됩니다.
  • 바인드없이 증가하는 시퀀스를 작성하려면 오름차순 시퀀스의 경우 MAXVALUE 매개 변수를 생략하거나 NOMAXVALUE를 지정하십시오.
  • 내림차순 시퀀스의 경우 MINVALUE 매개 변수를 생략하거나 NOMINVALUE를 지정하십시오.
  • 선언된 된 LIMIT에서 중지하는 시퀀스를 작성하려면 오름차순 시퀀스에 대해 MAXVALUE 매개 변수의 값을 지정하십시오.
  • 내림차순의 경우 MINVALUE 매개 변수의 값을 지정하십시오.
  • NOCYCLE은 시퀀스가 한계에 도달 한 후 시퀀스 번호를 생성하려고하면 오류가 발생합니다.
  • 선언한 LIMIT에 도달 한 후 다시 시작되는 시퀀스를 작성하려면 MAXVALUE 및 MINVALUE 매개 변수 모두에 값을 지정하십시오.
  • CYCLE로 지정하여 MINVALUE를 지정하지 않으면 기본값 인 NOMINVALUE (값 1)가 됩니다.
notifications_active 시퀀스 생성 예시
-- 시퀀스 생성
CREATE SEQUENCE [시퀀스명]
INCREMENT BY [증감숫자]
START WITH [시작숫자]
NOMINVALUE OR MINVALUE [최솟값]
NOMAXVALUE OR MAXVALUE [최댓값]
CYCLE OR NOCYCLE
CACHE OR NOCACHE
ORDER OR NOORDER
KEEP OR NOKEEP
SESSION OR GLOBAL;

1.1.1 INCREMENT BY[편집]

  • 시퀀스 번호 사이의 간격을 지정합니다.
  • 이 정수 값은 양수 또는 음수 일 수 있지만 0이 될 수 없습니다.이 값은 28 자리 이하일 수 있습니다.
  • 이 값의 절대 값은 MAXVALUE와 MINVALUE의 차이보다 작아야합니다.
  • 이 값이 음수이면 시퀀스가 내려갑니다. 값이 양수이면 시퀀스가 올라갑니다.
  • 이 절을 생략하면 간격은 기본적으로 1로 설정됩니다.

1.1.2 START WITH[편집]

  • 생성 할 첫 번째 시퀀스 번호를 지정하십시오.
  • 이 구문을 사용하여 최소값보다 큰 값에서 오름차순 시퀀스를 시작하거나 최대 값보다 작은 값에서 내림차순 시퀀스를 시작합니다.
  • 오름차순 시퀀스의 경우 기본값은 시퀀스의 최소값입니다. 내림차순 시퀀스의 경우 기본값은 시퀀스의 최대 값입니다. 이 정수 값은 28 자리 이하일 수 있습니다.

⚠️주의

이값은 최대or최소값에 도달한 후 오름차순 순환 시퀀스가 반드시 순환하는 값일 필요는 없습니다.

1.1.3 MAXVALUE[편집]

  • 시퀀스가 생성할 수 있는 최대값을 지정합니다.
  • 이 정수 값은 28자리 이하일 수 있습니다.
  • MAXVALUE는 START WITH 이상이어야 하고 MINVALUE보다 커야 합니다.

1.1.4 NOMAXVALUE[편집]

  • NOMAXVALUE를 지정하여 오름차순의 경우 최대값 1027을, 내림차순의 경우 -1을 지정하십시오.
  • 이것이 기본값입니다.

1.1.5 MINVALUE[편집]

  • 시퀀스의 최소값을 지정합니다.
  • 이 정수 값은 28자리 이하일 수 있습니다.
  • MINVALUE는 START WITH보다 작거나 같아야 하고 MAXVALUE보다 작아야 합니다.

1.1.6 NOMINVALUE[편집]

  • NOMINVALUE를 지정하여 오름차순의 경우 최소값 1을, 내림차순의 경우 -1026을 지정하십시오.
  • 기본값입니다.

1.1.7 CYCLE[편집]

  • CYCLE을 지정하여 시퀀스가 ​​최대값 또는 최소값에 도달한 후에도 계속해서 값을 생성함을 나타냅니다.
  • 오름차순 시퀀스는 최대값에 도달한 후 최소값을 생성합니다.
  • 내림차순 시퀀스가 ​​최소값에 도달한 후 최대값을 생성합니다.

1.1.8 NOCYCLE[편집]

  • NOCYCLE을 지정하여 시퀀스가 ​​최대값 또는 최소값에 도달한 후 더 이상 값을 생성할 수 없음을 나타냅니다.
  • 이것이 기본값입니다.

1.1.9 CACHE[편집]

  • 빠른 액세스를 위해 데이터베이스가 사전 할당하고 메모리에 보관하는 시퀀스 값 수를 지정합니다. 이 정수 값은 28 자리 이하일 수 있습니다.
  • 이 매개 변수의 최소값은 2입니다. 순환하는 시퀀스의 경우이 값은 순환의 값 수보다 작아야합니다.
  • 주어진 시퀀스 번호주기에 맞는 것보다 더 많은 값을 캐시 할 수 없습니다.
  • 따라서 CACHE에 허용되는 최대 값은 다음 공식으로 결정된 값보다 작아야합니다.
    • (CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
  • 시스템 오류가 발생하면 커밋 된 DML 문에 사용되지 않은 모든 캐시 된 시퀀스 값이 손실됩니다.
  • 손실 된 값의 잠재적 인 수는 CACHE 매개 변수의 값과 같습니다.

⚠️주의

Oracle recommends using the CACHE setting to enhance performance if you are using sequences in an Oracle Real Application Clusters environment.

Oracle은 Oracle Real Application Clusters 환경에서 시퀀스를 사용하는 경우 성능 향상을 위해 CACHE 설정을 사용할 것을 권장합니다.

1.1.10 NOCACHE[편집]

  • 시퀀스 값이 사전 할당되지 않음을 표시하려면 NOCACHE를 지정하십시오.
  • CACHE와 NOCACHE를 모두 생략하면 데이터베이스는 기본적으로 20 개의 시퀀스 번호를 캐시합니다.

1.1.11 ORDER[편집]

  • 요청 순서대로 시퀀스 번호가 생성되도록 ORDER를 지정하십시오.
  • 이 절은 시퀀스 번호를 타임 스탬프로 사용하는 경우 유용합니다.
  • 보증 순서는 일반적으로 기본 키를 생성하는 데 사용되는 시퀀스에 중요하지 않습니다.
  • ORDER는 Oracle Real Application Clusters를 사용하는 경우 정렬된 생성을 보장하기 위해서만 필요합니다.
  • 그외 모드를 사용하는 경우 시퀀스 번호는 항상 순서대로 생성됩니다.

1.1.12 NOORDER[편집]

  • 요청 순서대로 생성되는 시퀀스 번호를 보장하지 않으려면 NOORDER 를 지정하십시오.
  • 미지정시 default 입니다.

1.1.13 KEEP OR NOKEEP[편집]

  • 프로그램 테스트 혹은 성능 시험을 하는 동안 현재값 보호 여부

1.1.14 SESSION OR GLOBAL[편집]

  • 세션을 기준으로 각각 시퀀스 번호가 생성 GLOBAL은 여러 사용자가 사용 가능

1.2 시퀀스 수정[편집]

1.3 시퀀스 관리 프로시져 (DBA 용)[편집]

DB간 데이터 이관 후 시퀀스를 테이블 별 사용하는 컬럼의 최대값+1 으로 변경 
  • 시퀀스 변경관리 절차
  1. 시퀀스 관리 테이블 생성
  2. 시퀀스 변경 프로시져 생성
  3. 시퀀스관리 프로시져 실행 목록 뷰 생성

1.3.1 설치[편집]

1.3.1.1 시퀀스 변경 프로시져 (DBA 용)[편집]
CREATE OR REPLACE PROCEDURE SP_DBA_RESET_SEQ(P_OWNER VARCHAR2, P_SEQ_NAME VARCHAR2, P_NEW_NUM NUMBER, P_EXEC NUMBER DEFAULT 0)
IS
    V_NEXTVAL NUMBER;
    V_SQL_RESET     VARCHAR2(4000);
    V_SQL_INIT    VARCHAR2(4000);
    V_SQL_NEXTVAL  VARCHAR2(4000);
BEGIN
    -- 1. 1씩 증가하도록 시퀀스 변경  
    V_SQL_INIT := 'ALTER SEQUENCE '||P_OWNER||'.'||P_SEQ_NAME||' INCREMENT BY 1';
    EXECUTE IMMEDIATE V_SQL_INIT;  -- 증가값 초기화
    
    -- 2.최대 시퀀스 값 채번 
    V_SQL_NEXTVAL := 'SELECT '||P_OWNER||'.'||P_SEQ_NAME||'.NEXTVAL FROM DUAL';
    EXECUTE IMMEDIATE V_SQL_NEXTVAL INTO V_NEXTVAL;
    
    -- 3.시퀀스 증가값을 신규변경값 - 현재 최대값(2번에서 채번한값) => 차이값만큼 변경 하기 위해서 
    V_SQL_RESET  := 'ALTER SEQUENCE '||P_OWNER||'.'||P_SEQ_NAME||' INCREMENT BY '||TO_CHAR(P_NEW_NUM - V_NEXTVAL);
        DBMS_OUTPUT.ENABLE(1000);
        DBMS_OUTPUT.PUT_LINE('V_NEXTVAL = '||V_NEXTVAL);
    
    IF(P_EXEC = 1) THEN
        -- 3-1. 차이값 만큼 조정 
        EXECUTE IMMEDIATE V_SQL_RESET;  -- GAP만큼 조정

        -- 3-2. 시퀀스 조정값 에서 1회 증가 실행     
        EXECUTE IMMEDIATE V_SQL_NEXTVAL INTO V_NEXTVAL; -- 채번 1회 실행

        -- 4.증가값 초기화 실행 
        EXECUTE IMMEDIATE V_SQL_INIT;   -- 증가값 초기화
    ELSE 
        DBMS_OUTPUT.ENABLE(1000);
        DBMS_OUTPUT.PUT_LINE(V_SQL_RESET||';');
        DBMS_OUTPUT.PUT_LINE(V_SQL_NEXTVAL||';');
        DBMS_OUTPUT.PUT_LINE(V_SQL_INIT||';');
    END IF;   
END;
1.3.1.2 시퀀스 관리 테이블 (DBA 용)[편집]
CREATE TABLE TB_MIG_SEQ_MGR
(
  OWNER           VARCHAR2(30 BYTE),
  TABLE_NAME      VARCHAR2(50 BYTE),
  TABLE_HAN_NAME  VARCHAR2(50 BYTE),
  USE_YN          VARCHAR2(2 BYTE),
  SEQ_NAME        VARCHAR2(30 BYTE),
  SEQ_COL         VARCHAR2(30 BYTE),
  SEQ_RULE        VARCHAR2(50 BYTE),   -- 시퀀스 적용 룰 => MAX(컬럼)+1 형태로 저장 
--   CACHE           VARCHAR2(10 BYTE), 미사용 
  COL_TYPE        VARCHAR2(30 BYTE),
  SEQ_COMMENT     VARCHAR2(200 BYTE),
  CREATED         DATE                          DEFAULT SYSDATE               NOT NULL
)
;

COMMENT ON TABLE TB_MIG_SEQ_MGR IS '시퀀스 관리 테이블';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.OWNER IS 'OWNER';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.TABLE_NAME IS '테이블명';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.TABLE_HAN_NAME IS '테이블한글명';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.USE_YN IS '사용여부';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.SEQ_NAME IS '시퀀스명';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.SEQ_COL IS '시퀀스컬럼';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.SEQ_RULE IS '시퀀스규칙';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.CREATED IS '생성일시';
COMMENT ON COLUMN TB_MIG_SEQ_MGR.SEQ_COMMENT IS '시퀀스_코멘트';


CREATE UNIQUE INDEX PK_TB_MIG_SEQ_MGR ON TB_MIG_SEQ_MGR
(OWNER, TABLE_NAME, SEQ_NAME)
;
ALTER TABLE TB_MIG_SEQ_MGR ADD (
  CONSTRAINT PK_TB_MIG_SEQ_MGR
  PRIMARY KEY
  (OWNER, TABLE_NAME, SEQ_NAME)
  USING INDEX PK_TB_MIG_SEQ_MGR
  ENABLE VALIDATE);

1.3.2 프로시져 자동 수행 뷰 (DBA 용)[편집]

  • 시퀀스 관리테이블(TB_MIG_SEQ_MGR)의 테이블별 시퀀스사용컬럼(SEQ_RULE) 의 MAX(컬럼) 값 조회(XML쿼리 이용)
  • (예시) SEQ_RULE : MAX(컬럼)+1 으로 사용하면 입력된 데이터 다음값으로 시퀀스 적용됨.
CREATE OR REPLACE FORCE VIEW V_SEQ_MAKE_QRY
AS
    SELECT OWNER
         , TABLE_NAME
         , SEQ_NAME
         , COL_TYPE
         ,    'EXEC SP_DBA_RESET_SEQ(P_OWNER => '''
           || OWNER
           || ''',P_SEQ_NAME => '''
           || SEQ_NAME
           || ''',P_NEW_NUM =>'
           || C1
           || ',P_EXEC => 1);'    SP
      FROM (SELECT A.OWNER
                 , A.TABLE_NAME
                 , SEQ_NAME
                 , COL_TYPE
                 , REGEXP_REPLACE (
                       EXTRACT (
                           DBMS_XMLGEN.getXmlType (
                                  'SELECT TO_CHAR('
                               || SEQ_RULE
                               || ')||'' ''  C FROM '
                               || A.OWNER
                               || '.'
                               || A.TABLE_NAME)
                         , '/ROWSET/ROW/C').getStringVal ()
                     , '<C>|</C><C>|</C>'
                     , ''                                             -- 태그 제거
                         )    C1
              FROM TB_MIG_SEQ_MGR  A
                   LEFT JOIN DBA_TAB_COLUMNS B
                       ON     A.OWNER = B.OWNER
                          AND A.TABLE_NAME = B.TABLE_NAME
                          AND A.SEQ_COL = B.COLUMN_NAME
             WHERE     A.OWNER = 'SCOOT'
               AND A.COL_TYPE IN ('NUMBER', 'VARCHAR2')
               AND A.USE_YN = 'Y'
            UNION ALL
            -- MAX값이 아닌 초기값으로 지정할 경우 
            SELECT A.OWNER
                 , A.TABLE_NAME
                 , SEQ_NAME
                 , COL_TYPE
                 , A.SEQ_RULE     C1
              FROM TB_MIG_SEQ_MGR A
             WHERE A.OWNER = 'SCOTT' 
               AND (A.COL_TYPE IS NULL));

1.4 사용법[편집]

1. 시퀀스생성 목록 조회

SELECT * 
  FROM V_SEQ_MAKE_QRY
WHERE TABLE_NAME = '테이블' -- 테이블명 
-- AND SEQ_NAME = '시퀀스'  -- 시퀀스명    
;


2.시퀀스 변경 프로시져 실행 예시

EXEC SP_DBA_RESET_SEQ(P_OWNER => '오너',P_SEQ_NAME => '시퀀스명',P_NEW_NUM =>변경할 시퀀스값 ,P_EXEC => 1);