"오라클 시퀀스 관리"의 두 판 사이의 차이
DB CAFE
(새 문서: = 오라클 시퀀스 변경 관리 = == 설치 == == 사용법 ==) |
|||
1번째 줄: | 1번째 줄: | ||
= 오라클 시퀀스 변경 관리 = | = 오라클 시퀀스 변경 관리 = | ||
== 설치 == | == 설치 == | ||
+ | === 시퀀스 변경 프로시져 == | ||
+ | <source lang=sql> | ||
+ | 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; | ||
+ | </source> | ||
+ | === 시퀀스 관리 테이블 == | ||
+ | |||
+ | <source lang=sql> | ||
+ | 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), | ||
+ | SEQ_MAX_NUM VARCHAR2(30 BYTE), | ||
+ | CREATED DATE DEFAULT SYSDATE NOT NULL, | ||
+ | CACHE VARCHAR2(10 BYTE), | ||
+ | COL_TYPE VARCHAR2(30 BYTE), | ||
+ | SEQ_COMMENT VARCHAR2(200 BYTE) | ||
+ | ) | ||
+ | ; | ||
+ | |||
+ | 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); | ||
+ | </source> | ||
+ | === 프로시져 자동 수행 뷰 == | ||
+ | |||
+ | <source lang=sql> | ||
+ | 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)); | ||
+ | </source> | ||
+ | |||
== 사용법 == | == 사용법 == | ||
+ | # 시퀀스생성 목록 조회 | ||
+ | <source lang=sql> | ||
+ | SELECT * FROM V_SEQ_MAKE_QRY; | ||
+ | </source> | ||
+ | # 시퀀스 변경 프로시져 실행 예시 | ||
+ | <source lang=sql> | ||
+ | EXEC SP_DBA_RESET_SEQ(P_OWNER => '오너',P_SEQ_NAME => '시퀀스명',P_NEW_NUM =>최대값 ,P_EXEC => 1); | ||
+ | </source> |
2019년 10월 16일 (수) 11:42 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 오라클 시퀀스 변경 관리[편집]
1.1 설치[편집]
1.2 = 시퀀스 변경 프로시져[편집]
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.3 = 시퀀스 관리 테이블[편집]
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),
SEQ_MAX_NUM VARCHAR2(30 BYTE),
CREATED DATE DEFAULT SYSDATE NOT NULL,
CACHE VARCHAR2(10 BYTE),
COL_TYPE VARCHAR2(30 BYTE),
SEQ_COMMENT VARCHAR2(200 BYTE)
)
;
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 = 프로시져 자동 수행 뷰[편집]
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 사용법[편집]
- 시퀀스생성 목록 조회
SELECT * FROM V_SEQ_MAKE_QRY;
- 시퀀스 변경 프로시져 실행 예시
EXEC SP_DBA_RESET_SEQ(P_OWNER => '오너',P_SEQ_NAME => '시퀀스명',P_NEW_NUM =>최대값 ,P_EXEC => 1);