오라클 시퀀스 관리
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.
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.
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.
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.
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.
Note:
This value is not necessarily the value to which an ascending cycling sequence cycles after reaching its maximum or minimum value.
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:
(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.
Note:
Oracle recommends using the CACHE setting to enhance performance if you are using sequences in an Oracle Real Application Clusters environment.
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.
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 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.
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.
1.2 시퀀스 수정[편집]
1.3 시퀀스 관리 프로시져[편집]
DB간 데이터 이관 후 시퀀스를 테이블 별 사용하는 컬럼의 최대값+1 으로 변경
- 시퀀스 변경관리 절차
- 시퀀스 관리 테이블 생성
- 시퀀스 변경 프로시져 생성
- 시퀀스관리 프로시져 실행 목록 뷰 생성
1.4 설치[편집]
1.4.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
V_SQL_INIT := 'ALTER SEQUENCE '||P_OWNER||'.'||P_SEQ_NAME||' INCREMENT BY 1';
EXECUTE IMMEDIATE V_SQL_INIT; -- 증가값 초기화
V_SQL_NEXTVAL := 'SELECT '||P_OWNER||'.'||P_SEQ_NAME||'.NEXTVAL FROM DUAL';
EXECUTE IMMEDIATE V_SQL_NEXTVAL INTO V_NEXTVAL;
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
EXECUTE IMMEDIATE V_SQL_RESET; -- GAP만큼 조정
EXECUTE IMMEDIATE V_SQL_NEXTVAL INTO V_NEXTVAL; -- 채번 1회 실행
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.4.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.4.3 프로시져 자동 수행 뷰[편집]
- 시퀀스 관리테이블(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.5 사용법[편집]
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);