ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE 普通表转换成分区表(在线重定义)

ORACLE 普通表转换成分区表(在线重定义)

原创 Linux操作系统 作者:lin_qin_007 时间:2009-04-26 11:49:07 0 删除 编辑
ORACLE 普通表转换成分区表(在线重定义)

                                                    

在线重定义简介

在一个高可用系统中,如果需要改变一个表的定义是一件比较棘手的问题,尤其是对于7×24系统。Oracle提供的基本语法基本可以满足一般性修改,但是对于把普通堆表改为分区表,把索引组织表修改为堆表等操作就无法完成了。而且,对于被大量DML语句访问的表,幸运的是,Oracle9i版本开始提供了在线重定义表功能,通过调用DBMS_REDEFINITION包,可以在修改表结构的同时允许DML操作。

 

基本原理:

把原来的表与现在的新表换个名字,换名操作仅仅是操作数据字典

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

  • 修改表的存储参数
  • 在同一个schema下将表移动到不同的tablespace
  • 增加并行查询支持
  • 添加或删除分区支持
  • 重建表以减少碎片
  • 将堆表变为索引组织表或相反
  • 添加或删除列

在线重定义的好处:

  • 重组表数据,压缩空间
  • 在线把表从一个表空间迁移到另一个表空间
  • 把表从普通表转换成分区表或者从分区表转换成普通表
  • 在线删除字段
  • 修改表的一些参数,一些需要重组才能起效的特性,如initrans参数

在线重定义表的需要以下权限:

  • execute_catalog_role(角色)
  • create any table
  • alter any table
  • drop any table
  • lock any table
  • select any table

在线重定义后的结果:

  • 原表已经根据中间表的结构重新定义
  • start_redef_table()finish_redef_table()之间定义在中间表上的触发器、索引、约束和授权,现在定义在原始重定义表上。中间表上disabled的约束在原始表上处于enabled状态。
  • 原始表上定义的触发器、索引、约束和授权建立在中间表上,并会在删除中间表时删除。原始表上原来enabled状态的索引,建立在中间表上,并处于disabled状态。
  • 任何定义在原始表上的存储过程和游标都会变为INVALID,当下次调用时后自动进行编译。
  • 如果执行过程中出现错误或者人为选择退出的话,可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE()过程。

在线重定义的一些限制:

  • 如果使用基于主键的方式,则原表后重定义后的表必须有相同的主键
  • 如果使用基于ROWID的方式,则不能是索引组织表
  • 如果原表上有物化视图或者物化视图日志,则不能在线重定义
  • 物化视图容器表或者高级队列表不能在线重定义
  • 索引组织表的溢出表不能在线重定义
  • 拥有BFILELOGN列的表不能在线重定义
  • Cluster中的表不能在线重定义
  • syssystem下的表不能在线重定义
  • 临时表不能在线重定义
  • 不支持水平数据子集
  • 在列映射时只能使用有确定结果的表达式,如子查询就不行
  • 如果中间表有新增列,则不能有NOT NULL约束
  • 原表和中间表之间不能有引用完整性
  • 在线重定义无法采用nologging

试验过程:

一、             准备工作

1、源表结构,已有3万条数据(必须有主键)

表名: NE_DEV_ROOM_TAB

    字段(略),按地区级区域编码F_AREA_CODE进行分区,表的主键为    ROOM_ID(机房ID)


2、创建中间表的脚本,包括索引等对象的脚本(尽量创建与源表结构相同)

CREATE TABLE ne_dev_room_tab_mid (
  ROOM_ID       NUMBER(
20) not Null,
  STATION_ID    NUMBER(
20) not null,
  ROOM_NAME     VARCHAR2(
1000),
  ROOM_LEVEL_ID NUMBER(
6),
  FF_ID         NUMBER(
20),
  AREA_CODE     VARCHAR2(
30),
  OFF_TYPE      NUMBER(
10),
  IS_DEL        CHAR(
1) default 'N',
  UNITE_TAG     CHAR(
1) default 'N',
  IP_ADDR       VARCHAR2(
30),
  F_AREA_CODE   VARCHAR2(
30))
     PARTITION BY LIST (F_AREA_CODE)
     (
     PARTITION area_code1
        VALUES (
'C03501')
        TABLESPACE EMDATA,
    PARTITION area_code2
        VALUES (
'C03502')
        TABLESPACE EMDATA,
     PARTITION area_code3
        VALUES (
'C03503')
        TABLESPACE EMDATA,
    PARTITION area_code4
        VALUES (
'C03504')
        TABLESPACE EMDATA,
    PARTITION area_code5
        VALUES (
'C03505')
        TABLESPACE EMDATA,
   PARTITION area_code6
        VALUES (
'C03506')
        TABLESPACE EMDATA,
   PARTITION area_code7
        VALUES (
'C03507')
        TABLESPACE EMDATA,
   PARTITION area_code8
        VALUES (
'C03508')
        TABLESPACE EMDATA,
   PARTITION area_code9
        VALUES (
'C03509')
        TABLESPACE EMDATA,
   Partition area_code0
        Values (Default)
        TABLESPACE EMDATA
     );
alter table NE_DEV_ROOM_TAB_MID
  add constraint PK_NE_DEV_MID_ROOM_ID primary key (ROOM_ID)
  Using Index Tablespace EMDATA_INDX;


Comment On Column NE_DEV_ROOM_TAB_MID.ROOM_ID Is
'主键ID';

 

3、检查源表所拥有的所有对象


二、进行在线重定义

1首先对要在线重定义的表自行验证,看该表是否可以重定义,如果不可以则会提示错误信息。

SQL>EXEC dbms_redefinition.can_redef_table('EOMS','NE_DEV_ROOM_TAB');
2
建个和源表表结构一样的分区表,作为中间表,见上面准备工作的脚本

CREATE TABLE ne_dev_room_tab_mid (
  ROOM_ID       NUMBER(
20) not Null,
  STATION_ID    NUMBER(
20) not null,
  ROOM_NAME     VARCHAR2(
1000),
  ROOM_LEVEL_ID NUMBER(
6),
  FF_ID         NUMBER(
20),
  AREA_CODE     VARCHAR2(
30),
  OFF_TYPE      NUMBER(
10),
  IS_DEL        CHAR(
1) default 'N',
  UNITE_TAG     CHAR(
1) default 'N',
  IP_ADDR       VARCHAR2(
30),
  F_AREA_CODE   VARCHAR2(
30))
     PARTITION BY LIST (F_AREA_CODE)
     (
     PARTITION area_code1
        VALUES (
'C03501')
        TABLESPACE EMDATA,
    PARTITION area_code2
        VALUES (
'C03502')
        TABLESPACE EMDATA,
     PARTITION area_code3
        VALUES (
'C03503')
        TABLESPACE EMDATA,
    PARTITION area_code4
        VALUES (
'C03504')
        TABLESPACE EMDATA,
    PARTITION area_code5
        VALUES (
'C03505')
        TABLESPACE EMDATA,
   PARTITION area_code6
        VALUES (
'C03506')
        TABLESPACE EMDATA,
   PARTITION area_code7
        VALUES (
'C03507')
        TABLESPACE EMDATA,
   PARTITION area_code8
        VALUES (
'C03508')
        TABLESPACE EMDATA,
   PARTITION area_code9
        VALUES (
'C03509')
        TABLESPACE EMDATA,
   Partition area_code0
        Values (Default)
        TABLESPACE EMDATA
     );

3、执行表的在线重定义
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
'EOMS','NE_DEV_ROOM_TAB','NE_DEV_ROOM_TAB_MID');

4、在中间表上建立触发器、索引和约束。

alter table NE_DEV_ROOM_TAB_MID
  add constraint PK_NE_DEV_MID_ROOM_ID primary key (ROOM_ID)
  Using Index Tablespace EMDATA_INDX;

Comment On Column NE_DEV_ROOM_TAB_MID.ROOM_ID Is '主键ID';


5
执行把中间表的内容和数据源表进行同步
 EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
'EOMS','NE_DEV_ROOM_TAB','NE_DEV_ROOM_TAB_MID');

6、利用user_tab_privs_made视图查询权限是否完整
7
执行结束在线定义过程
 EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(
'EOMS','NE_DEV_ROOM_TAB','NE_DEV_ROOM_TAB_MID');
7
、两张表进行核对完毕后,删除中间表

三、重定义失败情况的处理

如果执行在线重定义的过程中出错,可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行:DBMS_REDEFINITION.abort_redef_table以放弃执行在线重定义。

 

注意:当创建在线重定义成功后,应检查下表的原有索引、访问权限等对象是否都完整创建,如有遗漏,应手工创建。

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

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-04-13

  • 博文量
    4
  • 访问量
    10242