Online Redefinition在线重定义（一）

最新推荐文章于 2021-04-28 14:56:34 发布

couyifu7209

最新推荐文章于 2021-04-28 14:56:34 发布

阅读量156

点赞数

分区表在各行业的数据库都得到广泛应用，但是有些业务系统在设计阶段对系统数据和性能容量增长估计不足，或没有考虑到运维过程中的数据归档需求，往往没有对表做分区设计。在生产运行经过长时间的数据积累之后，才发现表越来越大，某些查询或插入数据的性能变得越来越慢，迫切需要做表分区改造。所以在生产系统运维过程中，经常遇到的一个需求是如何把一个数据量非常大的普通表改造成分区表。关于分区表的介绍，请参考博主的另一篇博文： http://blog.itpub.net/31015730/viewspace-2144183/

分区最早在oracle8.0版本引入，支持将一个表或索引物理地分解为多个更小、更可管理的部分。Oracle9i之前，表数据的整理是通过 alter table XXX move [tablespace XXX]进行的。如果表非常大，IO又不快的时候，move的过程可能相当漫长，不能算是HA特性。因此在HA的在线维护中，基本不会利用move来重组大型表，而且move后的表需要重建索引。在高可用系统中，如果需要改变一个表的定义是一件比较棘手的问题，尤其是对于7×24系统。Oracle提供的基本语法基本可以满足一般性修改，但是对于把普通堆表改为分区表，把索引组织表修改为堆表等操作就无法完成了。那么问题来了，业务系统往往都是7*24在线作业，改造的过程又必然涉及表结构的变动，如果对表进行重建，会对系统运行产生非常大的影响，通常会设置计划停机窗口来做这类维护操作。

将普通表改造为分区表主要有四种方式：

Export/import method

Insert with a subquery method

Partition exchange method

DBMS_REDEFINITION

具体参考：
How to Partition a Non-partitioned Table [ID 1070693.6]
http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218704.aspx

在本篇博文中主要介绍第四种方式：在线重定义，在线重定义技术允许数据库管理员在该表上有读写数据操作的情况下，非常灵活地修改表的物理属性、表数据、表结构。

更多关于DBMS_REDEFINITION的方法请参看作者另一篇博文：http://blog.itpub.net/31015730/viewspace-2144516/

在线重定义的应用场景和功能：

1、修改表的存储参数；

2、可以将表转移到其他表空间；

3、增加并行查询选项；

4、增加或删除分区；

5、重建表以减少碎片；

6、将堆表改为索引组织表或相反的操作；

7、增加或删除一个列。

调用DBMS_REDEFINITION包需要 EXECUTE_CATALOG_ROLE 角色，除此之外，还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的权限。

在线重定义表的步骤如下：

1.选择一种重定义方法：

存在两种重定义方法，一种是基于主键、另一种是基于ROWID。ROWID的方式不能用于索引组织表，而且重定义后会存在隐藏列M_ROW$$。默认采用主键的方式。

2.调用DBMS_REDEFINITION.CAN_REDEF_TABLE()过程，如果表不满足重定义的条件，将会报错并给出原因。

3.在用一个方案中建立一个空的中间表，根据重定义后你期望得到的结构建立中间表。比如：采用分区表，增加了COLUMN等。

4.调用DBMS_REDEFINITION.START_REDEF_TABLE()过程，并提供下列参数：被重定义的表的名称、中间表的名称、列的映射规则、重定义方法。

如果映射方法没有提供，则认为所有包括在中间表中的列用于表的重定义。如果给出了映射方法，则只考虑映射方法中给出的列。如果没有给出重定义方法，则认为使用主键方式。

5.在中间表上建立触发器、索引和约束，并进行相应的授权。任何包含中间表的完整性约束应将状态置为disabled。

当重定义完成时，中间表上建立的触发器、索引、约束和授权将替换重定义表上的触发器、索引、约束和授权。中间表上disabled的约束将在重定义表上enable。

6.（可选）如果在执行DBMS_REDEFINITION.START_REDEF_TABLE()过程和执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程直接在重定义表上执行了大量的DML操作，那么可以选择执行一次或多次的SYNC_INTERIM_TABLE()过程，以减少最后一步执行FINISH_REDEF_TABLE()过程时的锁定时间。

7.执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程完成表的重定义。这个过程中，原始表会被独占模式锁定一小段时间，具体时间和表的数据量有关。

执行完FINISH_REDEF_TABLE()过程后，原始表重定义后具有了中间表的属性、索引、约束、授权和触发器。中间表上disabled的约束在原始表上处于enabled状态。

8.（可选）可以重命名索引、触发器和约束。对于采用了ROWID方式重定义的表，包括了一个隐含列M_ROW$$。推荐使用下列语句经隐含列置为UNUSED状态或删除。

ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);

ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;

下面是进行重定义操作后的结果：

原始表根据中间表的属性和特性进行重定义；

START_REDEF_TABLE()和FINISH_REDEF_TABLE()操作之间在中间表上建立的触发器、索引、约束和授权，现在定义在原始表上。中间表上disabled的约束在原始表上处于enabled状态。

原始表上定义的触发器、索引、约束和授权建立在中间表上，并会在删除中间表时删除。原始表上原来enabled状态的索引，建立在中间表上，并处于disabled状态。

任何定义在原始表上的存储过程和游标都会变为INVALID，当下次调用时后自动进行编译。

如果执行过程中出现错误或者人为选择退出的话，可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE()过程。

其中UNAME 参数是指用户；

Oracle的普通表没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变；

下面，我们通过一个简单的例子，去看看如何使用dbms_redefinition包进行重定义操作。

实验环境：
操作系统：CentOS 7.1
数据库：Oracle 11.2.0.4

需求;在seiang用户下的t_wjq表上，增加多个字段

--在seiang用户下创建目标数据表t_wjq

SEIANG@seiang11g>select count(*) from dba_objects;

COUNT(*)

----------

86990

SEIANG@seiang11g>set timing on

SEIANG@seiang11g>create table t_wjq as select object_id,object_name,owner from dba_objects;

Table created.

Elapsed: 00:00:00.36

--查看表中的数据量

SEIANG@seiang11g>select count(*) from dba_objects;

COUNT(*)

----------

86991

Elapsed: 00:00:00.07

SEIANG@seiang11g>select count(*) from t_wjq;

COUNT(*)

----------

86991

Elapsed: 00:00:00.01

--在t_wjq表的object_id列上创建主键

SEIANG@seiang11g>alter table t_wjq add constraint pk_t_wjq_id primary key(object_id);

Table altered.

Elapsed: 00:00:00.26

--查看索引

SEIANG@seiang11g>select index_name,index_type,table_owner,table_name from user_indexes;

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME

------------------------------ --------------------------- ------------------------------ ------------------------------

PK_T_WJQ_ID NORMAL SEIANG T_WJQ

Elapsed: 00:00:00.22

--进行在线重定义的第一步，就是判断目标数据表是否可以进行重定义。可以使用dbms_redefinition包的can_redef_table方法进行判断

SEIANG@seiang11g>exec dbms_redefinition.can_redef_table( 'SEIANG','T_WJQ',dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18

注意该方法的第三个参数，使用主键还是rowid方法。本质上，Online Redefinition是使用物化视图Materialized View技术。过程定义记录就是主键和rowid两种策略。通常而言，我们还是推荐数据表有一个明确主键，也就是使用cons_use_pk。如果希望使用rowid，就使用dbms_redefinition.cons_use_rowid。

--通过了检查之后，就可以进行下一步，定义目标数据表格式。无论是何种变化，我们需要创建一个中间表t_wjq_interim，将我们“期望”的数据表定义实现在里面。其中包括表类型、列定义、分区定义和索引等。但是注意，约束（主外键）可以不定义在其中。

SEIANG@seiang11g>create table t_wjq_interim as select * from dba_objects where 1=0;

Table created.

Elapsed: 00:00:00.08

--查看表结构

SEIANG@seiang11g>desc t_wjq

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------

OBJECT_ID NOT NULL NUMBER

OBJECT_NAME VARCHAR2(128)

OWNER VARCHAR2(30)

SEIANG@seiang11g>

SEIANG@seiang11g>desc t_wjq_interim

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

NAMESPACE NUMBER

EDITION_NAME VARCHAR2(30)

显然，我们是希望给数据表T增加一些列。

--开始重定义过程。使用dbms_redefinition的start_redef_table方法

SEIANG@seiang11g>exec dbms_redefinition.start_redef_table('SEIANG','T_WJQ','T_WJQ_INTERIM',col_mapping => 'object_id object_id, object_name object_name, owner owner',options_flag => dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.10

此处注意参数col_mapping，这里以配对键值的方式定义了源数据表和中间表在列关系上的对应关系。每个列关系按照<源列名目标列名>的格式进行书写。

--对约束的处理，如果有需要转换的约束对象，我们可以使用copy_table_dependents方法将source数据表的约束拷贝到目标对象中。

SEIANG@seiang11g>declare

2 error_count number;

3 begin

4 error_count := 0;

5 dbms_redefinition.copy_table_dependents(

6 uname => 'SEIANG',

7 orig_table => 'T_WJQ',

8 int_table => 'T_WJQ_INTERIM',

9 copy_indexes => dbms_redefinition.cons_orig_params,

10 num_errors => error_count);

11 dbms_output.put_line(to_char(error_count)

12 );

13 end;

14 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.89

利用各种copy_xxx参数，我们可以精细的定义哪些约束依赖关系会被拷贝到目标表中。

--当结束之后，我们需要使用finish_redef_table方法结束定义过程。

SEIANG@seiang11g>exec dbms_redefinition.finish_redef_table('SEIANG','T_WJQ','T_WJQ_INTERIM');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.76

--重定义结束，再次查看表结构

SEIANG@seiang11g>desc t_wjq

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

NAMESPACE NUMBER

EDITION_NAME VARCHAR2(30)

SEIANG@seiang11g>

SEIANG@seiang11g>desc t_wjq_interim

Name Null? Type

----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------

OBJECT_ID NOT NULL NUMBER

OBJECT_NAME VARCHAR2(128)

OWNER VARCHAR2(30)

--查看相关索引

SEIANG@seiang11g>select index_name,index_type,table_owner,table_name from user_indexes;

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME

------------------------------ --------------------------- ------------------------------ ------------------------------

TMP$$_PK_T_WJQ_ID0 NORMAL SEIANG T_WJQ_INTERIM

PK_T_WJQ_ID NORMAL SEIANG T_WJQ

Elapsed: 00:00:00.23

关于对在线重定义过程中的细节，例如实现方式、中间DML操作同步、锁机制和各种方法的功能原理，将在作者接下来的博客中继续进行讨论。

作者：SEian.G（苦练七十二变，笑对八十一难）

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

转载于:http://blog.itpub.net/31015730/viewspace-2144544/

couyifu7209

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Online Redefinition在线重定义（一）

分区表在各行业的数据库都得到广泛应用，但是有些业务系统在设计阶段对系统数据和性能容量增长估计不足，或没有考虑到运维过程中的数据归档需求，往往没有对表做分区设计。在生产运行经过长时间的数据积累之后，才发现表越来越...
复制链接

扫一扫