Online Redefinition在线重定义（二）–单表复杂案例

在上一篇博文（Online Redefinition在线重定义（一））中，简单地介绍了Oracle在线重定义特性进行数据表在线结构变动操作。本篇博文将演示一个较复杂的案例，在案例的复杂变化中进行在线重定义及dbms_redefinition包各个关键方法的作用。

普通表改造分区表

—创建一张普通表t_wjq1

SEIANG@seiang11g>create table t_wjq1
as select object_id,object_name,created from dba_objects;

Table
created.

SEIANG@seiang11g>desc t_wjq1

Name
Null? Type

—————————————————————————————————————–
——–
—————————————————————————-

OBJECT_ID
NUMBER

OBJECT_NAME
VARCHAR2(128)

CREATED DATE

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

COUNT(*)

———-

86997

—在表t_wjq1的object_id列上创建主键

SEIANG@seiang11g>alter table t_wjq1
add constraint pk_t_wjq1_id primary key(object_id);

Table
altered.

SEIANG@seiang11g>

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

INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME

——————————
————————— ——————————
——————————

PK_T_WJQ1_ID NORMAL SEIANG T_WJQ1

需求：重定义的内容有以下几个：
（1）使用object_id进行分区
（2）created字段从date类型变为timestamp类型
（3）object_name字段改名为object_name_2。

—创建一张中间表t_wjq1_interim

SEIANG@seiang11g>create table
t_wjq1_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 (5000),

9
partition p2 values less than (10000),

10
partition p3 values less than (50000),

11
partition p4 values less than (maxvalue)

12 );

Table
created.

—查看中间表的分区情况

SEIANG@seiang11g>select
table_name,partition_name from user_tab_partitions;

TABLE_NAME PARTITION_NAME

——————————
——————————

T_WJQ1_INTERIM P4

T_WJQ1_INTERIM P3

T_WJQ1_INTERIM P2

T_WJQ1_INTERIM P1

—首先，查看t_wjq1表是否支持重定义操作

SEIANG@seiang11g>exec
dbms_redefinition.can_redef_table(‘SEIANG’,’T_WJQ1′,options_flag=>dbms_redefinition.cons_use_pk);

PL/SQL
procedure successfully completed.

Elapsed:
00:00:00.02

—开始重定义操作

SEIANG@seiang11g>exec
dbms_redefinition.start_redef_table(‘SEIANG’,’T_WJQ1′,’T_WJQ1_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.

Elapsed:
00:00:01.42

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

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

SEIANG@seiang11g>select mview_name,
container_name, query, REFRESH_METHOD from user_mviews;

MVIEW_NAME CONTAINER_NAME QUERY
REFRESH_

——————————
——————————
——————————————————————————–
——–

T_WJQ1_INTERIM T_WJQ1_INTERIM select object_id object_id,
object_name object_name_2, to_timestamp(created) cre FAST

Elapsed:
00:00:00.02

SEIANG@seiang11g>

SEIANG@seiang11g> select
master,log_table from user_mview_logs;

MASTER LOG_TABLE

——————————
——————————

T_WJQ1 MLOG$_T_WJQ1

Elapsed:
00:00:00.00

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

—查看原始表和中间表的数据量

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

COUNT(*)

———-

86997

Elapsed:
00:00:00.01

SEIANG@seiang11g>

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

COUNT(*)

———-

86997

Elapsed:
00:00:00.01

—没有DML操作，所以物化视图日志尚空

SEIANG@seiang11g>select * from
mlog$_t_wjq1;

no
rows selected

Elapsed:
00:00:00.00

SEIANG@seiang11g>select count(*)
from mlog$_t_wjq1;

COUNT(*)

———-

0

Elapsed:
00:00:00.00

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

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

（2）从源数据表中将数据加载到Interim中；

（3）创建物化视图日志；

如果在这个过程中，发生了DML操作，也就是说在start过程或者之后有DML操作，有新数据插入或修改，如下操作所示：

—查看t_wjq1表的数据量和最大的object_id值

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

COUNT(*)

———-

86997

Elapsed:
00:00:00.00

SEIANG@seiang11g>select
max(object_id) from t_wjq1;

MAX(OBJECT_ID)

————–

89700

Elapsed:
00:00:00.01

—模拟DML操作：在表t_wjq1中插入数据库

SEIANG@seiang11g>insert into t_wjq1
select object_id+90000,object_name,created from dba_objects;

87006
rows created.

Elapsed:
00:00:05.13

—再次查看原始表t_wjq1、中间表t_wjq1_interim以及物化视图日志试图的变化

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

COUNT(*)

———-

174003

Elapsed:
00:00:00.01

SEIANG@seiang11g>

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

COUNT(*)

———-

86997

Elapsed:
00:00:00.00

SEIANG@seiang11g>select count(*)
from mlog$_t_wjq1;

COUNT(*)

———-

87006

Elapsed:
00:00:00.01

发现：中间表的数据内容保持不变，并且物化视图日志积累了需要刷新的数据条目。此时存在数据的不一致和不统一。Oracle推荐要求使用sysnc_interim_table方法将重定义过程中出现的变化数据刷新。

—刷新8万多条数据，使用了超过四分钟时间。在这个过程中，我们可以看到刷新物化视图过程。

SEIANG@seiang11g>exec
dbms_redefinition.sync_interim_table(‘SEIANG’,’T_WJQ1′,’T_WJQ1_INTERIM’);

PL/SQL
procedure successfully completed.

Elapsed:
00:04:18.33

SEIANG@seiang11g>select * from
v$mvrefresh;

SID
SERIAL# CURRMVOWNER
CURRMVNAME

———-
———- ——————————- ——————————-

41 14059
SEIANG
T_WJQ1_INTERIM

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

SEIANG@seiang11g>alter session set
NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’;

Session
altered.

SEIANG@seiang11g>

SEIANG@seiang11g>select name,
LAST_REFRESH from user_mview_refresh_times;

NAME LAST_REFRESH

——————————
——————-

T_WJQ1_INTERIM 2017-09-06 13:59:57

SEIANG@seiang11g>select name,
LAST_REFRESH from user_mview_refresh_times;

NAME LAST_REFRESH

——————————
——————-

T_WJQ1_INTERIM 2017-09-06 14:22:20

—刷新结束后，发现t_wjq1_interim表和mlog$_t_wjq1日志表数据的变化

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

COUNT(*)

———-

174003

SEIANG@seiang11g>

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

COUNT(*)

———-

174003

SEIANG@seiang11g>

SEIANG@seiang11g>select count(*)
from mlog$_t_wjq1;

COUNT(*)

———-

0

说明无变化数据需要刷新了

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

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

SEIANG@seiang11g>declare

2
error_count number:=0;

3
begin

4
dbms_redefinition.copy_table_dependents(uname => ‘SEIANG’,orig_table
=> ‘T_WJQ1’,int_table => ‘T_WJQ1_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 /

PL/SQL
procedure successfully completed.

Elapsed:
00:00:05.29

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_WJQ1_ID0 NORMAL SEIANG T_WJQ1_INTERIM

PK_T_WJQ1_ID NORMAL SEIANG T_WJQ1

I_MLOG$_T_WJQ1 NORMAL SEIANG MLOG$_T_WJQ1

Elapsed:
00:00:00.04

SEIANG@seiang11g>select
master,log_table from user_mview_logs;

MASTER LOG_TABLE

——————————
——————————

T_WJQ1 MLOG$_T_WJQ1

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

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

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

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

（4）将源数据表和Interim数据表表名进行置换；

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

（6）释放开在中间表上的锁定；

SEIANG@seiang11g> exec
dbms_redefinition.finish_redef_table(‘SEIANG’,’T_WJQ1′,’T_WJQ1_INTERIM’);

PL/SQL
procedure successfully completed.

Elapsed:
00:00:02.11

SEIANG@seiang11g>select
master,log_table from user_mview_logs;

no
rows selected

Elapsed:
00:00:00.02

SEIANG@seiang11g>

SEIANG@seiang11g>select * from
mlog$_t_wjq1;

select
* from mlog$_t_wjq1

*

ERROR at
line 1:

ORA-00942:
table or view does not exist

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_WJQ1_ID0 NORMAL SEIANG T_WJQ1_INTERIM

PK_T_WJQ1_ID NORMAL SEIANG T_WJQ1

—检查重定义的结果

SEIANG@seiang11g>desc t_wjq1

Name
Null? Type

—————————————————————————————————————–
——–
—————————————————————————-

OBJECT_ID
NUMBER

OBJECT_NAME_2 VARCHAR2(128)

CREATED
TIMESTAMP(6)

SEIANG@seiang11g>

SEIANG@seiang11g>desc t_wjq1_interim

Name
Null? Type

—————————————————————————————————————–
——–
—————————————————————————-

OBJECT_ID
NOT NULL NUMBER

OBJECT_NAME
VARCHAR2(128)

CREATED
DATE

SEIANG@seiang11g> exec
dbms_stats.gather_table_stats(user,’T_WJQ1′,cascade => true);

PL/SQL
procedure successfully completed.

Elapsed:
00:00:00.76

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

SEIANG@seiang11g>select
table_name,partition_name from user_tab_partitions;

TABLE_NAME PARTITION_NAME

——————————
——————————

T_WJQ1 P1

T_WJQ1 P2

T_WJQ1 P3

T_WJQ1 P4

SEIANG@seiang11g>select
constraint_name,constraint_type,table_name from user_constraints;

CONSTRAINT_NAME C TABLE_NAME

——————————
– ——————————

TMP$$_PK_T_WJQ1_ID0 P T_WJQ1_INTERIM

PK_T_WJQ1_ID P T_WJQ1

之前的实验都是在单表情况下进行的在线重定义操作，但是，在实际的生产环境中，通常涉及到多表关系，例如外键关系表下的重定义，那有该如何处理呢？接下来的案例，作者将介绍多表关系下的在线重定义。

相关推荐: Redis中有哪些应用场景

本篇内容介绍了“Redis中有哪些应用场景”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！Redis是一个Key-Value的存储系统，使用ANSI C语言编写。…