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

发表回复