ORACLE 普通表转换成分区表（在线重定义）

最新推荐文章于 2021-04-10 18:58:59 发布

congqi5468

最新推荐文章于 2021-04-10 18:58:59 发布

阅读量92

点赞数

ORACLE 普通表转换成分区表（在线重定义）

在线重定义简介：

在一个高可用系统中，如果需要改变一个表的定义是一件比较棘手的问题，尤其是对于7×24系统。Oracle提供的基本语法基本可以满足一般性修改，但是对于把普通堆表改为分区表，把索引组织表修改为堆表等操作就无法完成了。而且，对于被大量DML语句访问的表，幸运的是，Oracle从9i版本开始提供了在线重定义表功能，通过调用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的方式，则不能是索引组织表
如果原表上有物化视图或者物化视图日志，则不能在线重定义
物化视图容器表或者高级队列表不能在线重定义
索引组织表的溢出表不能在线重定义
拥有BFILE，LOGN列的表不能在线重定义
Cluster中的表不能在线重定义
sys和system下的表不能在线重定义
临时表不能在线重定义
不支持水平数据子集
在列映射时只能使用有确定结果的表达式，如子查询就不行
如果中间表有新增列，则不能有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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13234423/viewspace-591841/