Oracle 12C R2-新特性-線上復原表結構修改

1 說明

In some cases, you might want to undo an online redefinition of a table. For example, the performance of operations on the table might be worse after the redefinition than it was before the redefinition. In these cases, you can roll back the table to its original definition while preserving all of the DML changes made to the table after it was redefined. Online table redefinition rollback is used mainly when redefinition changes the storage characteristics of the table, and the changes unexpectedly result in degraded performance.

在12.2中，在線上表重新定義之後，您可以在線上表重新定義之前将表滾回它的定義，同時保留對表的所有資料操作語言(DML)更改。通過DBMS_REDEFINITION包來實作。

可以快速復原掉表結構的修改操作，而且不會影響之前已經對表做的DML操作(增删改查)。

2 測試

2.1  建立測試表-修改之前的表結構

CREATE TABLESPACE cndba
   DATAFILE 'cndba01.dbf' SIZE 10M
   ONLINE;

CREATE TABLE lei.test_rollback
    (id    NUMBER(6) PRIMARY KEY,
     name  VARCHAR2(20))
   TABLESPACE cndba
   STORAGE (INITIAL 2M);      

2.2  驗證該表是線上重新的

BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE(
    uname        => 'lei',
    tname        =>'test_rollback',  --源表
    options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/      

2.3  建立臨時表

臨時表用來儲存修改表結構期間所有執行DML操作。

#建立一個壓縮表空間來存放臨時表

CREATE TABLESPACE tst_cmp_rollback_tbs
   DEFAULT ROW STORE COMPRESS ADVANCED
   DATAFILE 'tst_cmp_rollback_tbs.dbf' SIZE 10M
   ONLINE;      

 #建立臨時表

CREATE TABLE lei.int_tst_rollback
    (id    NUMBER(6) PRIMARY KEY,
     name  VARCHAR2(20))
   TABLESPACE tst_cmp_rollback_tbs
   STORAGE (INITIAL 2M);      

注意：表的字段名保持一緻。

2.4  啟動redefinition程序

SQL> BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
  uname           => 'lei',
  orig_table      => 'test_rollback',
  int_table       => 'int_tst_rollback',
  options_flag    => DBMS_REDEFINITION.CONS_USE_PK,
  enable_rollback => TRUE);  --一定是TRUE
END;
/

PL/SQL procedure successfully completed.      

2.5  拷貝依賴對象

DECLARE
num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname            => 'lei',
    orig_table       => 'test_rollback',
    int_table        => 'int_tst_rollback',
    copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => TRUE,
    num_errors       => num_errors);
END;
/

PL/SQL procedure successfully completed.      

2.6  查詢DBA_REDEFINITION_ERRORS視圖是否有錯誤

SET LONG  8000
SET PAGES 8000
COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A20
COLUMN BASE_TABLE_NAME HEADING 'Base Table Name' FORMAT A10
COLUMN DDL_TXT HEADING 'DDL That Caused Error' FORMAT A40

SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM
         DBA_REDEFINITION_ERRORS;
Object Name       Base Table DDL That Caused Error
-------------------- ---------- ----------------------------------------
SYS_C007360       TEST_ROLLB ALTER TABLE "LEI"."INT_TST_ROLLBACK" ADD
     ACK   CONSTRAINT "TMP$$_SYS_C0073600" PRIMARY
 KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXT
RANS 255
  TABLESPACE "CNDBA"  ENABLE NOVALIDATE      

主鍵和索引相關的錯誤資訊可以忽略。

2.7  同步資料到臨時表

BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
    uname      => 'lei',
    orig_table => 'test_rollback',
    int_table  => 'int_tst_rollback');
END;
/

PL/SQL procedure successfully completed.      

2.8  完成redefinition

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
    uname      => 'lei',
    orig_table => 'test_rollback',
    int_table  => 'int_tst_rollback');
END;
/

PL/SQL procedure successfully completed.      

這裡會瞬間鎖表test_rollback。

2.9  收尾工作(選一執行)

2.9.1 如果redefined table性能沒有達到預期效果，則復原redefinition

BEGIN
  DBMS_REDEFINITION.ROLLBACK(
    uname      => 'lei',
    orig_table => 'test_rollback',
    int_table  => 'int_tst_rollback');
END;
/      

2.9.2 相反，如果達到預期效果則終止復原操作

BEGIN
  DBMS_REDEFINITION.ABORT_ROLLBACK(
    uname      => 'lei',
    orig_table => 'test_rollback',
    int_table  => 'int_tst_rollback');
END;
/      

2.10  删除臨時表

SQL> drop table lei.int_tst_rollback;
Table dropped.