오라클 시퀀스 관리
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 오라클 시퀀스 관리[편집]
1.1 시퀀스 생성[편집]
If you specify none of the following clauses, then you create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying only INCREMENT BY -1 creates a descending sequence that starts with -1 and decreases with no lower limit.
다음 구문을 지정하지 않으면 1로 시작하여 MAX값 없이 1 씩 증가하는 오름차순 시퀀스를 생성합니다.
INCREMENT BY -1 만 지정하면 -1로 시작하고 MIN값 없이 감소하는 내림차순 시퀀스가 생성됩니다.
To create a sequence that increments without bound, for ascending sequences, omit the MAXVALUE parameter or specify NOMAXVALUE. For descending sequences, omit the MINVALUE parameter or specify the NOMINVALUE.
바인드없이 증가하는 시퀀스를 작성하려면 오름차순 시퀀스의 경우 MAXVALUE 매개 변수를 생략하거나 NOMAXVALUE를 지정하십시오.
내림차순 시퀀스의 경우 MINVALUE 매개 변수를 생략하거나 NOMINVALUE를 지정하십시오.
To create a sequence that stops at a predefined limit, for an ascending sequence, specify a value for the MAXVALUE parameter. For a descending sequence, specify a value for the MINVALUE parameter. Also specify NOCYCLE. Any attempt to generate a sequence number once the sequence has reached its limit results in an error.
선언된 된 LIMIT에서 중지하는 시퀀스를 작성하려면 오름차순 시퀀스에 대해 MAXVALUE 매개 변수의 값을 지정하십시오.
내림차순의 경우 MINVALUE 매개 변수의 값을 지정하십시오.
NOCYCLE은 시퀀스가 한계에 도달 한 후 시퀀스 번호를 생성하려고하면 오류가 발생합니다.
To create a sequence that restarts after reaching a predefined limit, specify values for both the MAXVALUE and MINVALUE parameters. Also specify CYCLE. If you do not specify MINVALUE, then it defaults to NOMINVALUE, which is the value 1.
선언한 LIMIT에 도달 한 후 다시 시작되는 시퀀스를 작성하려면 MAXVALUE 및 MINVALUE 매개 변수 모두에 값을 지정하십시오.
CYCLE로 지정하여 MINVALUE를 지정하지 않으면 기본값 인 NOMINVALUE (값 1)가 됩니다.
1.1.1 INCREMENT BY[편집]
Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.
시퀀스 번호 사이의 간격을 지정합니다.
이 정수 값은 양수 또는 음수 일 수 있지만 0이 될 수 없습니다.이 값은 28 자리 이하일 수 있습니다.
이 값의 절대 값은 MAXVALUE와 MINVALUE의 차이보다 작아야합니다.
이 값이 음수이면 시퀀스가 내려갑니다. 값이 양수이면 시퀀스가 올라갑니다.
이 절을 생략하면 간격은 기본적으로 1로 설정됩니다.
1.1.2 START WITH[편집]
Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits.
생성 할 첫 번째 시퀀스 번호를 지정하십시오.
이 구문을 사용하여 최소값보다 큰 값에서 오름차순 시퀀스를 시작하거나 최대 값보다 작은 값에서 내림차순 시퀀스를 시작합니다.
오름차순 시퀀스의 경우 기본값은 시퀀스의 최소값입니다. 내림차순 시퀀스의 경우 기본값은 시퀀스의 최대 값입니다. 이 정수 값은 28 자리 이하일 수 있습니다.
⚠️주의
이값은 최대or최소값에 도달한 후 오름차순 순환 시퀀스가 반드시 순환하는 값일 필요는 없습니다.
1.1.3 MAXVALUE[편집]
Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.
1.1.4 NOMAXVALUE[편집]
Specify NOMAXVALUE to indicate a maximum value of 1027 for an ascending sequence or -1 for a descending sequence. This is the default.
1.1.5 MINVALUE[편집]
Specify the minimum value of the sequence. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.
1.1.6 NOMINVALUE[편집]
Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -1026 for a descending sequence. This is the default.
1.1.7 CYCLE[편집]
Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.
1.1.8 NOCYCLE[편집]
Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.
1.1.9 CACHE[편집]
Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:
빠른 액세스를 위해 데이터베이스가 사전 할당하고 메모리에 보관하는 시퀀스 값 수를 지정합니다. 이 정수 값은 28 자리 이하일 수 있습니다.
이 매개 변수의 최소값은 2입니다. 순환하는 시퀀스의 경우이 값은 순환의 값 수보다 작아야합니다.
주어진 시퀀스 번호주기에 맞는 것보다 더 많은 값을 캐시 할 수 없습니다.
따라서 CACHE에 허용되는 최대 값은 다음 공식으로 결정된 값보다 작아야합니다.
(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
If a system failure occurs, then all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.
시스템 오류가 발생하면 커밋 된 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[편집]
Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, then the database caches 20 sequence numbers by default.
시퀀스 값이 사전 할당되지 않음을 표시하려면 NOCACHE를 지정하십시오.
CACHE와 NOCACHE를 모두 생략하면 데이터베이스는 기본적으로 20 개의 시퀀스 번호를 캐시합니다.
1.1.11 ORDER[편집]
Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
요청 순서대로 시퀀스 번호가 생성되도록 ORDER를 지정하십시오.
이 절은 시퀀스 번호를 타임 스탬프로 사용하는 경우 유용합니다.
보증 순서는 일반적으로 기본 키를 생성하는 데 사용되는 시퀀스에 중요하지 않습니다.
ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.
ORDER는 Oracle Real Application Clusters를 사용하는 경우 정렬된 생성을 보장하기 위해서만 필요합니다.
그외 모드를 사용하는 경우 시퀀스 번호는 항상 순서대로 생성됩니다.
1.1.12 NOORDER[편집]
Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.
요청 순서대로 생성되는 시퀀스 번호를 보장하지 않으려면 NOORDER를 지정하십시오.
미지정시 default 입니다.
1.2 시퀀스 수정[편집]
1.3 시퀀스 관리 프로시져[편집]
DB간 데이터 이관 후 시퀀스를 테이블 별 사용하는 컬럼의 최대값+1 으로 변경
- 시퀀스 변경관리 절차
- 시퀀스 관리 테이블 생성
- 시퀀스 변경 프로시져 생성
- 시퀀스관리 프로시져 실행 목록 뷰 생성
1.3.1 설치[편집]
1.3.1.1 시퀀스 변경 프로시져[편집]
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 시퀀스 관리 테이블[편집]
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 프로시져 자동 수행 뷰[편집]
- 시퀀스 관리테이블(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);