oracle在线重定义表

      oracle在线重定义表无评论

         7*24的业务运营系统中，对一个表进行在线重定义将是很棘手的事，因为业务实时的在对这个表进行进行插入，删除修改等操作；Oracle9i开始引入在线重定义表(Redefine Tables Online)的功能,通过调用dbms_redefinition包，可以在修改表结构的同时允许DML操作。

在线重定义表的主要功能：

1  修改表的存储参数

2  在同一个schema下将表移动到不同的tablespace

3  增加并行查询支持

4  添加或删除分区支持

5  重建表以减少碎片

6  将堆表变为索引组织表或相反

7  添加或删除列

在线重定义表需要的权限：

execute_catalog_role

create any table

alter any table

drop any table

lock any table

select any table

在线重定义后的结果:

1  原表已经根据中间表的结构重新定义

2  在start_redef_table()和finish_redef_table()之间定义在中间表上的触发器、索引、约束和授权，现在定义在原始重定义表上。中间表上disabled的约束在原始表上处于enabled状态。

3  原始表上定义的触发器、索引、约束和授权建立在中间表上，并会在删除中间表时删除。原始表上原来enabled状态的索引，建立在中间表上，并处于disabled状态。

4  任何定义在原始表上的存储过程和游标都会变为INVALID，当下次调用时后自动进行编译。

5  如果执行过程中出现错误或者人为选择退出的话，可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE()过程。

在线重定义的一些限制：

Ø  如果使用基于主键的方式，则原表后重定义后的表必须有相同的主键

Ø  如果使用基于ROWID的方式，则不能是索引组织表

Ø  如果原表上有物化视图或者物化视图日志，则不能在线重定义

Ø  物化视图容器表或者高级队列表不能在线重定义

Ø  索引组织表的溢出表不能在线重定义

Ø  拥有BFILE，LOGN列的表不能在线重定义

Ø  Cluster中的表不能在线重定义

Ø  sys和system下的表不能在线重定义

Ø  临时表不能在线重定义

Ø  不支持水平数据子集

Ø  在列映射时只能使用有确定结果的表达式，如子查询就不行

Ø  如果中间表有新增列，则不能有NOT NULL约束

Ø  原表和中间表之间不能有引用完整性

Ø  在线重定义无法采用nologging

在线重定义的一般步骤：

1、   检查是否满足条件

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(‘EDW’, ‘CTL_LOG’, DBMS_REDEFINITION.CONS_USE_PK);

–检查原表是否满足在线重定义的条件

2、   生成中间表

CREATE TABLE CTL_LOG_TMP

(ID       INTEGER PRIMARY KEY,

PROC_NAME VARCHAR2(32),

CTL_DEMO  VARCHAR2(36),

CTL_TIME  VARCHAR2(19),

MARK      VARCHAR2(1000));

–中间表的表结构要与原表一致，可以新加列

3、   开始重定义

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(‘EDW’, ‘CTL_LOG’,’CTL_LOG_TMP’,’ID ID,PROC_NAME PROC_NAME,CTL_TIME CTL_TIME,CTL_DEMO CTL_DEMO’);

–首先将临时表转换成一个以原表为基础的物化视图；表结构不完全一致也没关系，只要能映射正确即可

4、   更新重定义过程中的DML

EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(‘EDW’,’CTL_LOG’,’CTL_LOG_TMP’);

–可以选择执行一次或多次，同步中间表与原表的数据(为防止在表的重定义过程中，对源表进行的DML操作),缩短执行DBMS_REDEFINITION.FINISH_REDEF_TABLE时的锁表时间

5、   完成重定义

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘EDW’,’CTL_LOG’,’CTL_LOG_TMP’);

–将原表变成与手工新建的中间临时表表结构相同，并将中间临时表从物化视图再转换成表(与当初的原表结构相同)

6、   删除中间表

DROP TABLE CTL_LOG_TMP;

7、   异常处理

EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE(‘EDW’,’CTL_LOG’,’CTL_LOG_TMP’);

–如果在线重定义失败，那么就必须必须调用DBMS_REDEFINITION.ABORT_REDEF_TABLE来释放快照。

说明：

一般会有这个需求，需要将在线大表（5G以上）更改为分区表，Dbms_Redefinition在线重定义非常慢，可能需要好几个小时，大表又是实时的业务操作，这就存在很大的失败风险。有人就曾经在线重定义过程中失败导致业务瘫痪，建议还是少用，或是不用，知道有这么回事就可以了。

个人觉得如下步骤比较保险，但是需要停机

  1. a) export the table
  2. b) create a new empty table that has the partition definition on it
  3. c) import the table with IGNORE=Y

发表回复