Oracle Online Redefinition在线重定义（中）

上篇中，我们简单地介绍了如何使用Oracle在线重定义特性进行数据表Online的结构变动操作。本篇我们从一个较复杂的案例出发，讨论复杂变化情况下如何进行Online Redefinition，以及dbms_redefinition包各个关键方法的作用。

 

3、一个分区表的重定义动作

 

我们定义一个数据表T。

 

 

SQL> create table t as select object_id, object_name, created from dba_objects;

Table created

 

SQL> desc t;

Name        Type          Nullable Default Comments

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

OBJECT_ID   NUMBER        Y                        

OBJECT_NAME VARCHAR2(128) Y                        

CREATED     DATE          Y                        

 

SQL> alter table t add constraint pk_t primary key (object_id);

Table altered

 

SQL> select count(*) from t;

 

  COUNT(*)

----------

     75192

 

 

期望的重定义目标有几个：首先使用object_id进行分区、created字段从date类型变为timestamp类型。另外object_name字段改名为object_name_2。中间定义表如下：

 

 

（分区，created变类型，object_name字段改名）

 

SQL> create table t_interim

  2  (object_id number,

  3   object_name_2 varchar2(128),

  4   created timestamp

  5  )

  6  partition by range(object_id)

  7  (

  8     partition p1 values less than (10000),

  9     partition p2 values less than (50000),

 10     partition p3 values less than (maxvalue)

 11  )

 12  ;

 

Table created

 

 

首先，判断是否可以进行重定义操作。

 

 

SQL> set serveroutput on;

SQL> exec dbms_redefinition.can_redef_table( 'SCOTT','T',options_flag => dbms_redefinition.cons_use_pk);

 

PL/SQL procedure successfully completed

 

 

启动重定义动作。

 

 

SQL> exec dbms_redefinition.start_redef_table('SCOTT','T','T_INTERIM',col_mapping => 'object_id object_id, object_name object_name_2, to_timestamp(created) created',options_flag => dbms_redefinition.cons_use_pk);

 

PL/SQL procedure successfully completed

 

SQL> set timing on;

 

 

注意这个col_mapping映射关系设置，如果存在列名转换，就在这里将列关系映射说明出来。如果需要进行字段类型转换，要书写函数关系将映射计算规则定义出来。

 

Oracle在线重定义的基础是物化视图技术。此时，我们检查试图user_mviews，可以看到有一个新的物化视图生成，并且存在对应的物化视图日志。

 

 

SQL> col query for a20;

SQL> select mview_name, container_name, query, REFRESH_METHOD from user_mviews;

 

MVIEW_NAME CONTAINER_NAME       QUERY                REFRESH_METHOD

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

T_INTERIM  T_INTERIM            select object_id obj         FAST

                                ect_id, object_name 

                                object_name_2, to_ti

                                mestamp(created) cre

                                ated from "SCOTT"."T

                                "   "T"             

 

 

Executed in 0.031 seconds

 

SQL> select master, log_table from user_mview_logs;

 

MASTER                         LOG_TABLE

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

T                              MLOG$_T

 

Executed in 0.016 seconds

 

 

Start方法创建了一个Fast刷新模式的物化视图对象t_interim。物化视图中最重要的物化视图日志，名称为MLOG$_T。

 

此时，数据表数据情况如下。

 

 

--源数据表和中间数据表已经实现同步

SQL> select count(*) from t;

 

  COUNT(*)

----------

     75192

 

Executed in 0.016 seconds

 

SQL> select count(*) from t_interim;

 

  COUNT(*)

----------

     75192

 

Executed in 0.031 seconds

 

--没有DML语句过程，物化视图日志尚空

SQL> select count(*) from mlog$_t;

 

  COUNT(*)

----------

         0

 

Executed in 0.015 seconds

 

 

综合上述内容，说明start_redef_table的作用是下面几个方面：

 

ü  以Interim数据表为名称，创建一个Fast刷新模式的物化视图对象；

ü  从源数据表中将数据加载到Interim中；

ü  创建物化视图日志；

 

如果在这个过程中，发生DML操作，也就是说在start过程和之后有DML操作，有新数据插入到其中。

 

 

SQL> select max(object_id) from t;

 

MAX(OBJECT_ID)

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

         76847

 

Executed in 0 seconds

 

SQL> insert into t select object_id+76847, object_name, created from dba_objects;

 

75199 rows inserted

Executed in 7.297 seconds

 

SQL> select count(*) from t;

  COUNT(*)

----------

    150391

 

Executed in 0.016 seconds

 

 

中间表的数据内容保持不变，并且物化视图日志积累了需要刷新的数据条目。

 

 

SQL> select count(*) from t_interim;

 

  COUNT(*)

----------

     75192

 

Executed in 0.016 seconds

 

SQL> select count(*) from mlog$_t;

 

  COUNT(*)

----------

     75199

 

Executed in 0.016 seconds

 

 

此时存在数据的不一致和不统一。Oracle推荐要求使用sysnc_interim_table方法将重定义过程中出现的变化数据刷新。

 

 

SQL> exec dbms_redefinition.sync_interim_table('SCOTT','T','T_INTERIM');

 

PL/SQL procedure successfully completed

 

Executed in 195.937 seconds

 

 

刷新7万左右数据，使用了超过三分钟时间。在这个过程中，我们可以看到刷新物化视图过程。

 

 

SQL> select * from v$mvrefresh;

 

       SID    SERIAL# CURRMVOWNER                     CURRMVNAME

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

        47         13 SCOTT                           T_INTERIM

 

 

刷新开始和结束过程，我们可以看到物化视图刷新过程中的时间变化。

 

 

SQL> select name, LAST_REFRESH from user_mview_refresh_times;

 

NAME       LAST_REFRESH

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

T_INTERIM  2013-9-10 9:07:01

 

SQL> select name, LAST_REFRESH from user_mview_refresh_times;

 

NAME       LAST_REFRESH

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

T_INTERIM  2013-9-10 9:15:28

 

 

结束后，我们发现interim表和mlog$_t日志表数据的变化。

 

 

SQL> select count(*) from t_interim;

 

  COUNT(*)

----------

    150391

 

Executed in 0.016 seconds

 

--无变化数据需要刷新了

SQL> select count(*) from mlog$_t;

 

  COUNT(*)

----------

         0

 

Executed in 0.016 seconds

 

 

综合上面的实验，我们知道方法sync_interim_table的实质是进行一次物化视图快速刷新。这个方法持续的时间根据不同数据量和物化视图刷新算法来决定，这个过程中，并不会引起很多锁定动作。而且，在在线重定义过程中，这个方法是可以重复执行多次的。

 

下面，需要将原有数据表中的约束关系刷新到目标结构上。

 

 

SQL> set serveroutput on;

SQL> declare

  2    error_count number:=0;

  3  begin

  4    dbms_redefinition.copy_table_dependents(uname => 'SCOTT',orig_table => 'T',int_table => 'T_INTERIM',

  5                                            copy_indexes => dbms_redefinition.cons_orig_params,

  6                                            num_errors => error_count);

  7    dbms_output.put_line(to_char(error_count));

  8  end;

  9  /

 

0

 

PL/SQL procedure successfully completed

 

 

Finish过程主要完成六个步骤操作：

 

ü  执行sysnc_interim_table命令，将中间表数据尽可能靠近源数据表；

ü  锁定源数据表T，使之后不能有任何变化发生在这个数据表上；

ü  再次执行sysnc_interim_table命令，这个时候执行的时间不会很长；

ü  将源数据表和Interim数据表表名进行置换；

ü  注销unregistered物化视图，并且删除掉物化视图日志；

ü  释放开在中间表上的锁定；

 

 

SQL> exec dbms_redefinition.finish_redef_table('SCOTT','T','T_INTERIM');

PL/SQL procedure successfully completed

 

Executed in 1.953 seconds

 

SQL> select count(*) from mlog$_t;

select count(*) from mlog$_t

 

ORA-00942: 表或视图不存在

 

 

检查处理结果。

 

 

--按照原定计划，数据表变化成功；

SQL> desc t;

Name          Type          Nullable Default Comments

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

OBJECT_ID     NUMBER        Y                        

OBJECT_NAME_2 VARCHAR2(128) Y                        

CREATED       TIMESTAMP(6)  Y                        

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

Executed in 2.719 seconds

 

 

分区和主键对象实现成功。

 

 

SQL> select partition_name from user_tab_partitions where table_name='T';

 

PARTITION_NAME

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

P1

P2

P3

 

Executed in 0.062 seconds

 

 

SQL> select constraint_name, constraint_type from user_constraints where table_name='T';

 

CONSTRAINT_NAME                CONSTRAINT_TYPE

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

PK_T                           P

 

Executed in 0.062 seconds

 

 

我们之前讨论的都是单表情况下的处理，如果是涉及到多表关系，例如外键关系表下的重定义，是怎么处理呢？

 

时间： 2024-08-22 15:14:22

Oracle Online Redefinition在线重定义（中）的相关文章

Oracle Online Redefinition在线重定义（上）

面对越来越多的7*24系统,运维人员进行工作可用的时间窗口变的越来越小.就在有限的时间窗口中,硬件检修.网络改造配置占据了很多时间.对数据库对象进行日常维护,越来越成为我们需要关注的问题.   进行数据重排.表分区.字段类型修改.字段增改这样的操作,在开发和测试环境上是比较容易进行的.即使数据表很大,操作耗时可能会很高,我们也能够通过一些非技术的手段赢取操作时间窗.但是对于投产系统而言,操作过程中的长时间锁定可能是业务不能接受的.这个时候,就可以考虑Oracle的一些Online操作技术.  

Oracle Online Redefinition在线重定义（下）

在之前的文章中,我们看到了如何处理单表在线重定义过程.本篇我们来看一下如何进行关联表的重定义过程.   4.外键关系表重定义   我们先创建出实验数据表.     SQL> create table t_master as select owner, table_name, tablespace_name, status from dba_tables where 1=0; Table created   SQL> alter table t_master add constraint pk_

oracle 11g的在线重定义简介

在Oracle9i出现之前,你只能通过MOVE或导出和导入的方式来进行表的重定义,因此表重定义的过程可能相当漫长或者说是一个离线过程,在此期间应用程序对该表的操作将失败.除了这个,如果用exp,我们也不能保证exp的时候该表的数据没有改变(除非单用户),而imp更是一个漫长的过程.为了解决这个问题,Oracle9i在其DBMS_REDEFINITION软件包中引入了在线重定义功能.这个特性对24*7的数据库系统来说非常重要,使用这个技术DBA可以在保持表允许DML语句的情况下修改结构,比如添加列

【Oracle】利用在线重定义的方式改变普通表为分区表

将普通表改为分区表有如下几种方式: 1 创建一个和原表一样的分区表A_NEW ;    将insert A_NEW SELECT * FROM A;    将表A 命名为A_OLD 将A_NEW 该名为A; 2 利用在先重定义的方式!也是接下来要介绍的方法! 第一种方式需要停止应用对A的写访问;使用在线重定义的方式可以对应用透明! 测试例子如下:1 创建测试表 创建普通表: @bigtab.sql --tom 的大表创建脚本! 创建中间分区PART_TAB,使用PART_TAB来替换bigtab

Oracle表的在线重定义（一）

好处: When a table is redefined online, it is accessible to both queries and DML during much of the redefinition process.  内部机制: Typically, the table is locked in the exclusive mode only during a very small window that is independent of the size of the

oracle 10g在线重定义新特性：关联对象自动重命名（二）

9i的在线重定义存在一个问题,执行完在线重定义后,表的名称虽然保持不变,但是索引.约束.触发器等关联对象的名称会发生变化,有时候这会带来一定的问题,而要在事后手工修改,会比较麻烦. 10g的在线重定义解决这个问题.如果对象是利用COPY_TABLE_DEPENDENTS创建的,那么这些关联的对象在重定义操作完成后,自动改为原始的名称.如果是手工创建的关联对象,则可以利用REGISTER_DEPENDENT_OBJECT过程,所有执行了REGISTER_DEPENDENT_OBJECT过程的关联对

【Oracle】在线重定义表

Oracle从9i版本开始提供了在线重定义表功能,通过调用DBMS_REDEFINITION包,可以在修改表结构的同时允许DML操作. 在线重定义表具有以下功能: 1 修改表的存储参数 2 可以将表转移到其他表空间 3 增加并行查询选项 4 增加或删除分区 5 重建表以减少碎片 6 将堆表改为索引组织表或相反的操作 7 增加或删除一个列 调用DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色和以下权限: CREATE ANY TABLE ALTER ANY TA

oracle在线重定义包DBMS_REDIFINITION #

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

Oracle在线重定义失败后的处理

普通表在线重定义为分区表过程中报错,数值范围超过了分区限制大小,那么想要重新对表进行在线重定义需要经过哪些步骤呢?这个例子记录了处理过程: SALES@ORCL>exec dbms_redefinition.start_redef_table('SALES', 'SALES', 'SALES_P'); BEGIN dbms_redefinition.start_redef_table('SALES', 'SALES', 'SALES_P'); END; * ERROR at line 1: OR