在oracle 9i下在线重定义表

9i提供了联机重定义表的方法，可以让你在基本不影响原表的DML情况下修改表结构。

实际上，联机重定义表并不是完全的联机重定义，在最后交换表名的时候会短暂地锁定原表和中间表，但这个过程很短暂，相对于传统方法来说，这是一个进步。

/*
9i提供了联机重定义表的方法，可以让你在基本不影响原表的DML情况下修改表结构。

利用联机重定义功能可以在线实现如下功能：
1：修改表的存储参数
2：移动该表到相同Schema 下的不同表空间内(如：把scott用户下的表从一个空间移动到scott用户下的另一个表空间)
3：添加并行查询支持
4：添加或删除分区
5：重建表以便减少碎片
6：在普通表和索引组织(index-organized)表之间互相转换
7：添加或删除列，重新定义列的数据类型
8：添加/删除索引
9：做一个从普通表到分区表之间的转换操作.
10：修改约束

需要的权限：
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLE

限制条件：
你必须有足以维护两份表格拷贝的空间。
你不能更改主键栏。
表必须有主键。
必须在同一个schema中进行表格重定义。
在重定义操作完成之前，你不能对新加列加以NOT NULL约束。
表格不能包含LONG、BFILE以及用户类型（UDT）。
不能重定义链表（clustered tables）。
不能在SYS和SYSTEM 用户中重定义表格。
不能用具体化视图日志（materialized view logs）来重定义表；不能重定义含有具体化视图的表。
不能在重定义过程中进行横向分集（horizontal subsetting）。

基本步骤如下

第一步：利用dbms_redefinition.can_redef_table过程检查该表是否能被在线重定义，如果这一步不抛出异常，说明该表是可以在线重定义的。
第二步：创建一个与原表类似的空表结构，用于重定义该表，这里叫做中间表。
在这里你可以定义表的新列名、新数据类型、列顺序、存储参数等。注意，为了提高效率，在这一步不要建立索引和约束。
第三步：用dbms_redefinition.start_redef_table procedure定义重构开始
这个过程将会自动执行如下操作：
1、插入所有行从原有表到中间表
2、创建MLOG$_xxx快照与快照日志，临时存储DML语句直到完成。

该过程可以输入如下参数信息
用户的名称
原表的名称
中间表的名称
原表和中间表的列的映射关系（这个参数默认值是null，表示原表和中间表的字段按原表的顺序一一对应。但如果需要在中间表添加、删除字段，修改字段数据类型、改变字段顺序，则必须要把这个参数填写好。不同字段间用“，”分隔，作映射时，对应的字段先写原表字段，然后再写中间表的字段）。
例1：添加字段，并改变列的数据类型：
原表 a(id int ,name varchar2(10))
中间表b(id number,new_name varchar2(10),sex int)
exec dbms_redefinition.start_redef_table procedure('suk','a','b','name new_name,id id')
或者exec dbms_redefinition.start_redef_table procedure('suk','a','b','id id,name new_name')

再如同时添加、删除字段：
原表 a(id int ,name varchar2(10))
中间表b(id number,sex int)
exec dbms_redefinition.start_redef_table procedure('suk','a','b','id id')

映射时可以使用一些简单函数，如
exec dbms_redefinition.start_redef_table procedure('suk','a','b','to_char(id) id')
所以，如果数据量很大的话，这一步会比较慢。

第四步：调用DBMS_REDEFINITION.SYNC_INTERIM_TABLE过程同步原表与中间表的数据（但我们应该把这一步放在为中间表建立索引、约束等前面，这样可以提高效率），这一步不是必须的，如果省略这一步，在finish_redef_table也会执行这一步骤。

第五步：与原表一致，在中间表上面创建约束，索引，触发器
与原表一致（如果需要），中间表的对象权限被授予给别的对象
注意：在中间表建立外键约束时应该加上DISABLE关键字

第六步：用dbms_redefinition.finish_redef_table过程完成表的最终重定义
该过程将自动完成如下：
应用快照日志中的DML到中间表
互换原表与中间表的名字，包括所有可能出现的数据字典
但是需要注意的是，并不对换约束，索引，触发器的名称，这些需要手工修改
删除MLOG$_XXX
启用原来在中间表上的外键

同时，执行这一步时，oracle会短暂地LOCK原表和中间表

第七步：删除中间表、

第八步：如果是920以上，可以利用ALTER TABLE ... RENAME CONSTRAINT ...语句来修改约束名称，如果以下版本，就只有删除并重建了，当然，如果约束名称并不重要，也就无所谓了

第九步：如果重组织失败，那么你就必须采取特殊的步骤来让它重新开始。由于重定义过程需要创建表的快照，因此为了重新开始这一过程，你必须调用DBMS_REDEFINITION.ABORT_REDEF_TABLE来释放快照。
几个需要注意的地方：
在重定义表期间，不允许对中间表作任何DML操作
*/
--以下是一个利用9i的联机重定义表删除字段的简单过程

--原表结构
SQL> desc channel
Name Type Nullable Default Comments
------------------- -------------- -------- ------- --------
CHANNEL_ID NUMBER(16)
CHANNEL_NAME VARCHAR2(120)
PARENT_CHANNEL_ID NUMBER(16) Y
CHANNEL_URL VARCHAR2(256) Y
CHANNEL_DESCRIPTION VARCHAR2(4000) Y
STYLE_CLASS VARCHAR2(120) Y
CONFIGURE_FILE VARCHAR2(120) Y
CHANNEL_SEQUENCE NUMBER(4) Y 9999
CHANNEL_LEVEL NUMBER(2) Y
CREATE_DATE DATE SYSDATE
SELF_TAG_ID NUMBER(8) Y
CHANNEL_CODE VARCHAR2(120) Y

--创建中间表
SQL> create table channel_test as select * from channel where 1=2;

Table created

SQL> alter table channel_test drop column STYLE_CLASS;

Table altered

SQL> desc channel_test
Name Type Nullable Default Comments
------------------- -------------- -------- ------- --------
CHANNEL_ID NUMBER(16)
CHANNEL_NAME VARCHAR2(120)
PARENT_CHANNEL_ID NUMBER(16) Y
CHANNEL_URL VARCHAR2(256) Y
CHANNEL_DESCRIPTION VARCHAR2(4000) Y
CONFIGURE_FILE VARCHAR2(120) Y
CHANNEL_SEQUENCE NUMBER(4) Y
CHANNEL_LEVEL NUMBER(2) Y
CREATE_DATE DATE
SELF_TAG_ID NUMBER(8) Y
CHANNEL_CODE VARCHAR2(120) Y

--检测是否能在线重定义表
SQL> exec dbms_redefinition.can_redef_table('suk','channel');

PL/SQL procedure successfully completed

--开始重定义表
SQL> exec dbms_redefinition.start_redef_table('suk','channel','channel_test','channel_id,CHANNEL_NAME,PARENT_CHANNEL_ID,CHANNEL_URL,CHANNEL_DESCRIPTION,CONFIGURE_FILE,CHANNEL_SEQUENCE,CHANNEL_LEVEL,CREATE_DATE,SELF_TAG_ID,CHANNEL_CODE');

PL/SQL procedure successfully completed

--完成重定义表
SQL> exec dbms_redefinition.finish_redef_table('suk','channel','channel_test');

PL/SQL procedure successfully completed

--查看重定义后的表结构，可以看到表channel的结构已经改变
SQL> desc channel
Name Type Nullable Default Comments
------------------- -------------- -------- ------- --------
CHANNEL_ID NUMBER(16)
CHANNEL_NAME VARCHAR2(120)
PARENT_CHANNEL_ID NUMBER(16) Y
CHANNEL_URL VARCHAR2(256) Y
CHANNEL_DESCRIPTION VARCHAR2(4000) Y
CONFIGURE_FILE VARCHAR2(120) Y
CHANNEL_SEQUENCE NUMBER(4) Y
CHANNEL_LEVEL NUMBER(2) Y
CREATE_DATE DATE
SELF_TAG_ID NUMBER(8) Y
CHANNEL_CODE VARCHAR2(120) Y

参考文档：

piner 9i新特性之——在线表格重定义研究

tahiti.oracle.com

Oracle的在线重定义表提供了一种显著增强系统可用性的机制, 在表的在线重定义过程中, 大部分时间对DML操作没有影响, 只有一小段时间里, 这个表以独占模式被锁定, 表以独占模式被锁定的时间是确定的, 和表的大小以及重定义的复杂程度无关.

在线重定义表可用于如下场合:

    修改表的存储参数
    在同一模式下将表移动到不同的表空间
    增加对并行查询的支持
    添加或删除分区
    重新创建表以减少分片
    将堆组织的表改变为索引组织表
    添加或删除列

在线重定义表由DBMS_REDEFINITION包完成, 执行这个包需要用户有EXECUTE_CATALOG_ROLE角色以及CREATE/ALTER/DROP/LOCK/SELECT ANY TABLE, CREATE ANY TRIGGER和CREATE ANY INDEX的权限.

表的在线重定义需要执行如下步骤:

    1. 选择在线重定义表的类型在线重定义表分为两种类型:

    重定义的第一种方法是使用主键或者伪主键进行重定义。伪主键是具有NOT NULL约束的列的组合, 在表中, 伪主键必须是唯一的, 也可以理解为伪主键是没有PRIMARY KEY约束的主键. 使用这种方法时, 重定义前后表的(伪)主键列必须相同. 这种方法更好一些, 并且也是默认的.

    重定义的第二种方法是使用rowid. 使用这种方法时, 表不能被重定义为索引组织表. 并且, 重定义后的表会有一个隐藏的列M_ROW$$, 重定义表后, 最好删除该列, 或将该列标识为UNUSED.

    2. 调用DBMS_REDEFINITION.CAN_REDEF_TABLE()过程验证这个表可被重定义   参数OPTIONS_FLAG指定要使用的重定义的方法. 如果这个表不可以被在线重定义, 这个过程会指明原因.

    3. 创建一个中间表这个中间表和将被重定义的表在同一schema下, 其定义与重定义后的表相同.

    4. 调用DBMS_REDEFINITION.START_REDEF_TABLE()过程参数如下: 被重定义的表, 中间表, 列影射关系, 重定义方法, 用语排序的列, 指定ORDER BY列.

    5. 在中间表上创建(克隆)依赖对象, 包括触发器, 索引, 授权, 约束. 可以使用COPY_TABLE_DEPENDENTS过程或者手工方式创建这些依赖对象.

    6. 执行DBMS_REDEFINITION.START_REDEF_TABLE()过程, 这个过程是可选的, 执行这一过程, 将会把在原始表上进行的DML操作在中间表上执行, 执行这一过程将减少在下一步骤执行的时间.

    7. 执行 DBMS_REDEFINITION.FINISH_REDEF_TABLE过程完成表的重定义. 在这个过程中, 将被重定义的表被以独占模式锁定, 这个过程与表中的数据无关. 但是FINISH_REDEF_TABLE将回等待所有依赖的DML操作完成, 才开始重定义操作.

    8. 可选, 删除rowid方式建立的隐藏列M_ROW$$, 或者将该列设置为unused.

如果由于某些原因希望终止在线重定义表, 可使用DBMS_REDEFINITION.ABORT_REDEF_TABLE()过程.

在线重定义表的结果:

    原始表被重定义为中间表的属性和功能.

    在执行START_REDEF_TABLE()和执行FINISH_REDEF_TABLE()期间, 定义在中间表上的触发器, 授权, 索引以及约束将被定义在重定义后的表上. 引用中间表的参考约束将引用重定义后的表, 并且设置为enabled.

    定义在原始表上的触发器, 授权, 索引以及约束将被定义在中间表上, 删除中间表时, 这些对象将被删除. 引用原始表的参考性约束, 将引用中间表, 并被设置为disable.
    定义才原始表上的PL/SQL过程和游标, 将在下次使用时将重新验证.