ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 在oracle 9i下在线重定义表

在oracle 9i下在线重定义表

原创 Linux操作系统 作者:space6212 时间:2019-06-17 08:15:06 0 删除 编辑

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

参考文档:

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

tahiti.oracle.com

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-63741/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    163981