oracle 9i下在线重定义表【转】

转自：http://www.cnblogs.com/rootq/archive/2010/01/13/1646396.html

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

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

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

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

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

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


限制条件：
·你必须有足以维护两份表格拷贝的空间。
·你不能更改主键栏。
·表格必须有主键。
·必须在同一个大纲中进行表格重定义。
·在重定义操作完成之前，你不能对新加栏加以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，表示原表和中间表的字段按原表的顺序一一对应。但如果需要在中间表添加、删除字段，修改字段数据类型、改变字段顺序，则必须要把这个参数填写好。不同字段间用都好分隔。
作映射时，对应的字段先写原表字段，然后再写中间表的字段。
如添加字段，并改变列的数据类型：
原表 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