DBMS_REDEFINTIONを使用してオンラインでテーブルを再定義する

次の手順を使用して、テーブルをオンラインで再定義します。

1. by keyのいずれかで再定義方法を選択します またはby rowids 。

   キー別 ：再定義に使用する主キーまたは疑似主キーを選択します。疑似主キーは、すべてのコンポーネント列にNOT NULLが含まれる一意のキーです。 制約。この方法では、再定義の前後のテーブルのバージョンは、同じプライマリキー列で構成されている必要があります。これは、再定義の推奨されるデフォルトの方法です。

   rowid別： 使用可能なキーがない場合は、この方法を使用してください。このメソッドでは、M_ROW$$という名前の非表示の列 は、テーブルの再定義後のバージョンに追加されます。再定義が完了したら、この列を削除するか、未使用としてマークする必要があります。 COMPATIBLEの場合 が10.2.0以上に設定されている場合、再定義の最終フェーズでは、この列が未使用として自動的に設定されます。次に、ALTER TABLE ... DROP UNUSED COLUMNSを使用できます。 これを削除するステートメント。このメソッドは、索引構成表では使用できません。

2. CAN_REDEF_TABLEを呼び出して、テーブルをオンラインで再定義できることを確認します 手順。テーブルがオンライン再定義の候補ではない場合、この手順により、テーブルをオンラインで再定義できない理由を示すエラーが発生します。

3. 必要なすべての論理属性と物理属性を使用して、空の暫定テーブルを（再定義するテーブルと同じスキーマに）作成します。

4. 再定義されるテーブルのすべてのインデックス、制約、許可、およびトリガーを使用して暫定テーブルを作成する必要はありません。これは、COPY_TABLE_DEPENDENTSを使用して自動的に実行できます。 手順。

5. より大きなテーブルのパフォーマンスを向上させるために、次のコマンドを使用して並列に設定できます。

    ALTER SESSION force parallel dml parallel degree-of-parallelism;
   
    ALTER SESSION force parallel query parallel degree-of-parallelism;
   

6. FINISH_REDEF_TABLE コマンドはテーブルの再定義を完了します。この手順の間、元のテーブルは、元のテーブルのデータ量に関係なく、短時間排他モードでロックされます。ただし、FINISH_REDEF_TABLE 再定義を完了する前に、保留中のすべてのDML操作がコミットされるのを待ちます。

7. rowidsを使用した場合 再定義とCOMPATIBLE 初期化パラメータが10.1.0以下に設定されている場合は、非表示の列M_ROW$$を削除する必要があります 再定義されたテーブルに追加されました。次のコマンドを使用して、列を「未使用」に設定することもできます。

    ALTER TABLE <table_name> SET UNUSED (M_ROW$$);
   

   COMPATIBLEの場合 10.2.0以上の場合、この非表示の列は自動的にUNUSEDに設定されます 再定義が完了したとき。次に、ALTER TABLE ... DROP UNUSED COLUMNSを使用して列を削除できます。 声明。暫定テーブルに対する実行中のクエリが完了するのを待ってから、暫定テーブルを削除します。

[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

SQL> CREATE TABLE TEST1 ( ID        NUMBER(10) ,
                      ENAME     VARCHAR2(10),
                      SAL       NUMBER(10) ) ;

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.

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.

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.

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

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.

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.

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.