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.

签到成功

CNDBA社区

Oracle 12C R2-新特性-Online table redefinition rollback