온라인 테이블 재정의를 사용하면 데이터를 사용할 수 없게 만들지 않고 프로덕션에서 Oracle® 테이블을 재구성할 수 있습니다. 임시 테이블을 사용하여 데이터를 이동하는 것이 편할 수도 있지만 더 나은 솔루션이 있습니다.
소개
테이블을 재구성하는 동안 데이터를 준비하고 이동하면 특정 기간 동안 테이블과 데이터를 모두 사용할 수 없게 되며 이는 비즈니스에 불리한 상황입니다. DBMS_REDEFINITION 패키지는 다음 이미지와 같이 하루를 저장합니다.
목적
주기적으로 다음과 같은 이유로 Oracletable의 논리적 또는 물리적 구조를 수정해야 합니다.
- 쿼리 또는 DML(데이터 조작 언어) 성능 향상
- 응용 프로그램 변경을 수용하기 위해
- 저장소 관리
Oracle Database는 테이블의 가용성에 큰 영향을 미치지 않으면서 테이블 구조를 수정하는 메커니즘을 제공합니다. 이를 온라인 테이블 재정의라고 합니다. 온라인으로 테이블을 재정의하면 기존의 테이블 재정의 방법에 비해 성능이 크게 향상됩니다.
테이블이 온라인으로 재정의되면 대부분의 재정의 프로세스 동안 쿼리와 DML 모두에 액세스할 수 있습니다. 테이블은 테이블의 크기 및 재정의의 복잡성과 무관한 짧은 기간 동안만 단독 모드로 잠겨 있습니다. 재정의 프로세스는 사용자에게 완전히 투명합니다.
온라인 테이블 재정의에는 재정의 중인 테이블에서 현재 사용 중인 공간과 거의 동일한 여유 공간이 필요합니다.
테이블을 재구성하는 방법에는 여러 가지가 있습니다. 다운타임이 문제인 경우 DBMS_REDEFINITION 패키지는 이 작업에 가장 적합한 옵션입니다.
온라인으로 테이블 재정의
온라인에서 표를 재정의하려면 다음 단계를 따르십시오.
-
by key재정의 방법을 선택합니다. 또는by rowids.키별 :정의에 사용할 기본 키 또는 의사 기본 키를 선택합니다. 의사 기본 키는 모든 구성 요소 열에
NOT NULL이 포함된 고유 키입니다. 제약. 이 방법의 경우 재정의 전후의 테이블 버전은 동일한 기본 키 열로 구성되어야 합니다. 이것은 재정의의 기본 및 기본 방법입니다.rowid 기준: 사용 가능한 키가 없는 경우 이 방법을 사용하십시오. 이 방법에서
M_ROW$$라는 이름의 숨겨진 열 , 테이블의 재정의 후 버전에 추가됩니다. 정의가 완료된 후 이 열을 삭제하거나 사용하지 않은 것으로 표시해야 합니다.COMPATIBLE인 경우 10.2.0 이상으로 설정하면 재정의의 마지막 단계에서 자동으로 이 열을 사용하지 않은 것으로 설정합니다. 그런 다음ALTER TABLE ... DROP UNUSED COLUMNS를 사용할 수 있습니다. 문을 삭제하십시오. 인덱스로 구성된 테이블에서는 이 방법을 사용할 수 없습니다. -
CAN_REDEF_TABLE을 호출하여 테이블을 온라인으로 재정의할 수 있는지 확인하십시오. 절차. 테이블이 onlineredefinition의 후보가 아닌 경우 이 절차는 테이블을 온라인으로 재정의할 수 없는 이유를 나타내는 오류를 발생시킵니다. -
원하는 모든 논리적 및 물리적 속성을 사용하여 빈 중간 테이블(정의할 테이블과 동일한 스키마에서)을 생성합니다.
-
재정의되는 테이블의 모든 인덱스, 제약 조건, 권한 부여 및 트리거를 사용하여 중간 테이블을 생성할 필요는 없습니다. 이는
COPY_TABLE_DEPENDENTS를 사용하여 자동으로 중단될 수 있습니다. 절차. -
더 큰 테이블에서 성능을 향상시키려면 다음 명령을 사용하여 병렬로 설정할 수 있습니다.
ALTER SESSION force parallel dml parallel degree-of-parallelism; ALTER SESSION force parallel query parallel degree-of-parallelism; -
FINISH_REDEF_TABLE명령은 테이블 재정의를 완료합니다. 이 절차 중에 원래 테이블은 원래 테이블의 데이터 양과 관계없이 짧은 시간 동안 단독 모드로 잠깁니다. 그러나FINISH_REDEF_TABLE재정의를 완료하기 전에 보류 중인 모든 DML 작업이 커밋될 때까지 기다립니다. -
rowids를 사용한 경우 재정의 및 귀하의COMPATIBLE초기화 매개변수가 10.1.0 이하로 설정되어 있으면 숨겨진 열M_ROW$$을 삭제해야 합니다. 재정의된 테이블에 추가되었습니다. 다음 명령을 사용하여 열을 "UNUSED"로 설정할 수도 있습니다.ALTER TABLE <table_name> SET UNUSED (M_ROW$$);COMPATIBLE인 경우 10.2.0 이상인 경우 이 숨겨진 열은 자동으로UNUSED로 설정됩니다. 재정의가 완료되면. 그런 다음ALTER TABLE ... DROP UNUSED COLUMNS를 사용하여 열을 삭제할 수 있습니다. 성명. 중간 테이블에 대해 장기 실행 쿼리가 완료될 때까지 기다렸다가 중간 테이블을 삭제합니다.
샘플 테이블 재정의
다음 섹션은 샘플 테이블 재정의를 위한 다양한 명령 및 출력의 예를 보여줍니다.
sqlplus 시작
다음 샘플은 sqlplus 시작을 보여줍니다. :
[oracle@vm215 ~]$ sqlplus amit/amit
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 29 05:44:44 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
데모 테이블 만들기
다음 샘플은 데모 테이블 이름 TEST1 생성을 보여줍니다. AMIT 아래 스키마.
SQL> CREATE TABLE TEST1 ( ID NUMBER(10) ,
ENAME VARCHAR2(10),
SAL NUMBER(10) ) ;
대량 행 삽입
다음 샘플은 대량 행 삽입 및 PPA_AGGGREGATE_TARGET 설정을 보여줍니다. AMIT에서 스키마를 최대값으로.
SQL> INSERT INTO AMIT.TEST1 SELECT ROWNUM, 'T'|| ROWNUM,
DBMS_RANDOM.VALUE(100000, 999999) FROM DUAL CONNECT BY LEVEL < 1000000;
999999 ROWS CREATED.
SQL> COMMIT;
COMMIT COMPLETE.
테스트를 위한 종속 개체 만들기
다음 샘플은 TEST1 테이블과 관련된 종속 개체를 만드는 방법을 보여줍니다. 행을 사용하여 온라인 재정의 중에 어떤 일이 발생하는지 확인할 수 있습니다.
보기 생성
SQL> CREATE VIEW TEST1_VW AS SELECT * FROM TEST1 ;
VIEW CREATED.
시퀀스 생성
SQL> CREATE SEQUENCE TEST_SEQ ;
SEQUENCE CREATED.
절차 생성
CREATE OR REPLACE PROCEDURE PROC1 (P_ID IN NUMBER)
AS V_ID NUMBER ;
BEGIN
SELECT SAL
INTO V_ID
FROM TEST1
WHERE ID = P_ID;
END;
/
PROCEDURE CREATED.
DML 트리거 생성
SQL> CREATE OR REPLACE TRIGGER AMIT_TRIG
BEFORE INSERT OR UPDATE ON TEST1
FOR EACH ROW
DECLARE
X NUMBER;
BEGIN
SELECT COUNT(*) INTO X
FROM TEST1
WHERE ID = :NEW.ID;
IF X > 0 THEN
RAISE_APPLICATION_ERROR(-20501, 'ID' || :NEW.ID || ' ALREADY EXISTS');
END IF;
END;
/
TRIGGER CREATED.
기본 키 생성
SQL> ALTER TABLE TEST1 ADD CONSTRAINT TEST1_ID_PK PRIMARY KEY (ID) ;
TABLE ALTERED.
재정의 전 상태 가져오기
SQL> COLUMN OBJECT_NAME FORMAT A20
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS ORDER BY OBJECT_NAME;SQL>
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
AMIT_TRIG TRIGGER VALID
PROC1 PROCEDURE VALID
TEST1 TABLE VALID
TEST1_ID_PK INDEX VALID
TEST1_VW VIEW VALID
TEST_SEQ SEQUENCE VALID
6 ROWS SELECTED.
재정의를 위한 테이블 확인
다음 샘플은 rowids를 사용하여 온라인에서 테이블을 재정의할 수 있는지 확인하는 방법을 보여줍니다. 또는 primary key :
기본 키 사용
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
rowid 사용
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_ROWID);
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
임시 테이블의 복제본 생성
다음 샘플은 종속 개체 없이 새 임시 테이블의 복제본을 만드는 방법을 보여줍니다.
SQL> CREATE TABLE TEST1_REORG AS SELECT * FROM TEST1 WHERE ROWNUM=5 ;
TABLE CREATED.
SQL> SELECT COUNT(*) FROM TEST1_REORG ;
COUNT(*)
----------
0
SQL> SELECT COUNT(*) FROM TEST1;
COUNT(*)
----------
999999
데이터베이스에 연결
다음 샘플은 권한 사용자를 사용하여 테이블 재정의 작업을 실행하여 연결하는 방법을 보여줍니다.
[oracle@vm215 ~]$ sqlplus / as sysdba
Sql*plus: release 11.2.0.3.0 production on sat oct 29 05:16:48 2016
Copyright (c) 1982, 2011, oracle. All rights reserved.
Connected to:
Oracle database 11g enterprise edition release 11.2.0.3.0 - 64bit production
With the partitioning, olap, data mining and real application testing options
재정의 시작
다음 샘플은 기본 키를 사용하여 재정의를 시작하는 방법을 보여줍니다.
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('AMIT','TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
종속 개체 복사
다음 샘플은 뷰, 기본 키, 뷰, 시퀀스 및 트리거와 같은 종속 개체를 자동으로 복사하는 방법을 보여줍니다. IGNORE_ERROR TRUE로 설정됨 COPY_TABLE_DEPENDENTS로 기본 키 위반을 피하기 위해 명령.
SQL> DECLARE
N PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('AMIT', 'TEST1','TEST1_REORG',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, N);
END;
/
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
오류 확인
다음 샘플은 DBA_REDEFINITION_ERRORS의 오류를 확인하는 방법을 보여줍니다. 보기:
SQL> COL OBJECT_NAME FOR A25
SET LIN200 PAGES 200
COL DDL_TEXT FOR A60
SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT
FROM DBA_REDEFINITION_ERRORS;
NO ROWS SELECTED
두 테이블 모두 검증
다음 샘플은 두 테이블의 행 수를 확인하고 중간 테이블과 동기화하는 방법을 보여줍니다.
SQL> SELECT COUNT(*) FROM AMIT.TEST1_REORG ;
COUNT(*)
----------
999999
SQL> SELECT COUNT(*) FROM AMIT.TEST1 ;
COUNT(*)
----------
999999
SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('AMIT', 'TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
재정의 완료
다음 샘플은 재정의 완료를 보여줍니다.
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('AMIT', 'TEST1', 'TEST1_REORG');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> COLUMN OBJECT_NAME FORMAT A40
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM DBA_OBJECTS
WHERE OWNER='AMIT';
OBJECT_NAME OBJECT_TYPE STATUS
--------------------- ------------------- -------
TEST1_VW VIEW INVALID
TEST_SEQ SEQUENCE VALID
PROC1 PROCEDURE VALID
TEST1 TABLE VALID
TEST1_REORG TABLE VALID
TEST1_ID_PK INDEX VALID
TMP$$_TEST1_ID_PK0 INDEX VALID
TMP$$_AMIT_TRIG0 TRIGGER INVALID
AMIT_TRIG TRIGGER INVALID
9 ROWS SELECTED.
오류 확인 및 스키마 재컴파일
다음 샘플은 이전 단계의 유효하지 않은 트리거 때문에 필요한 완전한 종속성을 사용하여 스키마를 다시 컴파일하는 방법을 보여줍니다.
SQL> EXEC UTL_RECOMP.RECOMP_SERIAL('AMIT') ;
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='AMIT';
OBJECT_NAME OBJECT_TYPE STATUS
---------------------------------------- ------------------- -------
TEST1_VW VIEW VALID
TEST_SEQ SEQUENCE VALID
PROC1 PROCEDURE VALID
TEST1 TABLE VALID
TEST1_REORG TABLE VALID
TEST1_ID_PK INDEX VALID
TMP$$_TEST1_ID_PK0 INDEX VALID
TMP$$_AMIT_TRIG0 TRIGGER VALID
AMIT_TRIG TRIGGER VALID
9 ROWS SELECTED.
임시 테이블 삭제
다음 샘플은 중간 테이블 삭제를 보여줍니다.
SQL> DROP TABLE AMIT.TEST1_REORG;
TABLE DROPPED.
결론
최종 사용자가 테이블 구조를 수정하고 동시에 액세스해야 하는 경우 DBMS_REFDEFINITION을 사용하십시오. .
이 기능은 다운타임 없이 데이터를 재구성하여 OLTP(Online Transaction Processing) 환경에서 다운타임으로 인한 고객의 문제를 방지합니다.
피드백 탭을 사용하여 의견을 남기거나 질문하십시오.