前段时间在ITPUB上看到有人问在线重定义是怎么实现的，一直没时间整理。

通常在线重定义有以下几个部分：

dbms_redefinition.can_redef_table

dbms_redefinition.start_redef_table

dbms_redefinition.sync_interim_table

dbms_redefinition.finish_redef_table

1. dbms_redefinition.can_redef_table:检查是否满足在线重定义的先决条件，如果没有报错，表示检查通过，如果检查没有通过，错误信息会提示在哪出的问题。
2. dbms_redefinition.start_redef_table：启动在线重定义。
3. dbms_redefinition.sync_interim_table：同步数据。
4. dbms_redefinition.finish_redef_table：结束在线重定义。

下面是实验过程及追踪后的trace部分内容，通过追踪，看看ORACLE是怎样实现在线重定义的。

实验环境：

win7 64位操作系统

oracle 11.2.0.1.0 for windows X86_64

步骤1.创建测试表并插入测试数据

SQL> create table t_test(id number,code varchar2(5),identifier varchar2(20));

表已创建。
SQL> insert into t_test values(1,'01','01-01-0001-000001');

已创建 1 行。

SQL> insert into t_test values(2,'02','02-01-0001-000001');

已创建 1 行。
SQL> insert into t_test values(3,'03','03-01-0001-000001');

已创建 1 行。
SQL> insert into t_test values(4,'04','04-01-0001-000001');

已创建 1 行。
SQL> commit;

提交完成。
SQL> create table t_temp(id number,code varchar2(5), identifier varchar2(20))
2 partition by list (code)
3 (partition part01 values('01'),
4 partition part02 values('02'),
5 partition part03 values('03'),
6 partition part04 values('04'),
7 partition part05 values(default)
8 );

表已创建。
SQL> alter table t_temp add constraint pk_temp_id primary key (id);

表已更改。
SQL> select * from tab;

TNAME                         TABTYPE  CLUSTERID
------------------------------                         -------   ----------
T_TEMP                         TABLE
T_TEST                         TABLE

步骤2.先决条件检查

SQL> exec dbms_redefinition.can_redef_table('stream', 't_test');

PL/SQL 过程已成功完成。

查看trace文件会看到，该过程ORACLE做的都是select操作，在检查是否支持在线重定义，下面是摘自trace文件的SQL语句。

SQL ID: csq97ktzjj1rz
Plan Hash: 0
BEGIN dbms_redefinition.can_redef_table('stream', 't_test'); END;
SQL ID: cjk1ffy5kmm5s
Plan Hash: 1964104430
select obj#
from
oid$ where user#=:1 and oid$=:2
SQL ID: 2yuyykvzhrp4t
Plan Hash: 901817615
SELECT redef_id
FROM
sys.redef_object$  WHERE obj_type = :1 AND obj_owner = :2 AND obj_name = :3
AND rownum = 1
SQL ID: 1gu8t96d0bdmu
Plan Hash: 2035254952
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,
nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,
t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,
t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),
nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),
nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,
ts.logicalread
from
tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
SQL ID: 7ng34ruy5awxq
Plan Hash: 2606284882
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
ist.logicalread
from
ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where
i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
SQL ID: 5n1fs4m2n2y0r
Plan Hash: 299250003
select pos#,intcol#,col#,spare1,bo#,spare2,spare3
from
icol$ where obj#=:1
SQL ID: 83taa7kaw59c1
Plan Hash: 3765558045
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
nvl(spare3,0)
from
col$ where obj#=:1 order by intcol#
SQL ID: 3w4qs0tbpmxr6
Plan Hash: 1224215794
select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3
from
cdef$ where robj#=:1
SQL ID: gx4mv66pvj3xz
Plan Hash: 1932954096
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3
from
cdef$ where obj#=:1
SQL ID: 53saa2zkr6wc3
Plan Hash: 3954488388
select intcol#,nvl(pos#,0),col#,nvl(spare1,0)
from
ccol$ where con#=:1
SQL ID: 9qj6r53cwban
Plan Hash: 2358287056
select c.name
from
sys.con$ c where c.con# = :1
SQL ID: br71g8xazf0kf
Plan Hash: 626680409
select 1
from
sys.cdc_change_tables$ where change_table_schema = :1  and change_table_name
= :2
SQL ID: 9gkq7rruycsjp
Plan Hash: 3362549386
select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused,
definitrans, defmaxtrans, deftiniexts, defextsize, defminexts, defmaxexts,
defextpct, deflists, defgroups, deflogging, spare1, mod(spare2, 256)
subparttype, mod(trunc(spare2/256), 256) subpartkeycols,
mod(trunc(spare2/65536), 65536) defsubpartcnt, mod(trunc(spare2/4294967296),
256) defhscflags, mod(spare3, 256) interval_dty, rowid, defmaxsize
from
partobj$ where obj# = :1
SQL ID: 90t64pvhxmza8
Plan Hash: 4233067210
select 1
from
sys.mlog$ where mowner = :1 and log = :2
SQL ID: 96g93hntrzjtr
Plan Hash: 2239883476
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln
from
hist_head$ where obj#=:1 and intcol#=:2

可以看到，ORACLE进行了一大堆的检查操作，其中也包括物化视图相关的检查，可以推测ORACLE的在线重定义是使用物化视图实现数据同步的，这些应该在同步数据的时候会看到。

步骤3.开始在线重定义

SQL> exec dbms_redefinition.start_redef_table('stream', 't_test', 't_temp');

PL/SQL 过程已成功完成。

此时查看t_temp表可以看到数据已经同步过来。

SQL> select * from t_temp;

ID CODE   IDENTIFIER
---------- ----- --------------------
1 01    01-01-0001-000001
2 02    02-01-0001-000001
3 03    03-01-0001-000001
4 04    04-01-0001-000001

此时向t_test表插入数据，不会同步到t_temp表，这个过程不会影响对t_test表的正常操作。

SQL> insert into t_test values(5,'05','05-01-0001-000001');

已创建 1 行。
SQL> commit;

提交完成。
SQL> select * from t_test;

ID CODE  IDENTIFIER

---------- ----- --------------------
1 01    01-01-0001-000001
2 02    02-01-0001-000001
3 03    03-01-0001-000001
4 04    04-01-0001-000001
5 05    05-01-0001-000001

SQL> select * from t_temp;

ID CODE  IDENTIFIER
---------- ----- --------------------
1 01    01-01-0001-000001
2 02    02-01-0001-000001
3 03    03-01-0001-000001
4 04    04-01-0001-000001

查看trace文件，会看到ORACLE干了好多的活，这次不只是select了，下面摘自trace文件的SQL语句,select语句就不摘录了：

SQL ID: a37q3yk2aa9bf
Plan Hash: 0
BEGIN dbms_redefinition.start_redef_table('stream', 't_test', 't_temp'); END;
SQL ID: 4m7m0t6fjcs5x
Plan Hash: 1935744642       --可以看到这里使用到了序列
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
cache=:7,highwater=:8,audit$=:9,flags=:10
where
obj#=:1
SQL ID: 45448dcmnf2a4
Plan Hash: 0             --创建物化视图日志
create table "STREAM"."MLOG$_T_TEST" ("ID" NUMBER, snaptime$$ date, dmltype$$
varchar2(1), old_new$$ varchar2(1), change_vector$$ raw(255),  xid$$ number)
pctfree 10  pctused 30
SQL ID: 0r1prja1au35b
Plan Hash: 0            --创建可更新物化视图日志
CREATE GLOBAL TEMPORARY TABLE "STREAM"."RUPD$_T_TEST" ("ID" NUMBER, dmltype$$
varchar2(1), snapid integer, change_vector$$ raw(255)) ON COMMIT PRESERVE
ROWS
SQL ID: 8f0wqd1ffcw8q
Plan Hash: 0           --将数据同步到T_TEMP表
INSERT INTO "STREAM"."T_TEMP"("ID","CODE","IDENTIFIER") SELECT "T_TEST"."ID",
"T_TEST"."CODE","T_TEST"."IDENTIFIER" FROM "STREAM"."T_TEST" "T_TEST"

 

上面只摘取部分trace内容，可见在这个过程中，ORACLE用到了物化视图。

步骤4.同步数据

下面再看下同步数据的过程。

SQL> exec dbms_redefinition.sync_interim_table('stream', 't_test', 't_temp');

PL/SQL 过程已成功完成。
SQL> select * from t_test;

ID CODE  IDENTIFIER
---------- ----- --------------------
1 01    01-01-0001-000001
2 02    02-01-0001-000001
3 03    03-01-0001-000001
4 04    04-01-0001-000001
5 05    05-01-0001-000001

SQL> select * from t_temp;

ID CODE  IDENTIFIER
---------- ----- --------------------
1 01    01-01-0001-000001
2 02    02-01-0001-000001
3 03    03-01-0001-000001
4 04    04-01-0001-000001
5 05    05-01-0001-000001

可见，在同步后，新插入的数据也同步到了T_TEMP表中，下面看下trace文件内容。

SQL ID: 1jvfq4a0r1yfu              --开始同步
Plan Hash: 0
BEGIN dbms_redefinition.sync_interim_table('stream', 't_test', 't_temp');
END;
SQL ID: 3dbyq4rdncaj8
Plan Hash: 0                      --将上文插入到T_TEST表的数据同步到T_TEMP
INSERT INTO "STREAM"."T_TEMP"  ("ID","CODE","IDENTIFIER")
VALUES
(:1,:2,:3)

步骤5.结束重定义

同步过程就是利用物化视图将新对T_TEST表的操作同步到T_TEMP表中，下面再看下finish的过程。

SQL ID: 1ku4fhcg4sfwd
Plan Hash: 0
BEGIN dbms_redefinition.finish_redef_table('stream', 't_test', 't_temp');
END;

在看下相关的trace会发现，在这个过程中，ORACLE在做一些delete和drop操作。

SQL ID: 4sa26ghd5gdvz
Plan Hash: 0
UPDATE "STREAM"."T_TEMP" SET "ID" = :1,"CODE" = :2,"IDENTIFIER" = :3
WHERE
"ID" = :1
SQL ID: 9n6hzq6rct7s8
Plan Hash: 0
drop materialized view "STREAM"."T_TEMP"
SQL ID: fnncq952g4ux5
Plan Hash: 0
drop view "STREAM"."T_TEMP"
SQL ID: 8qpg5jxwh8pws
Plan Hash: 0
drop table "STREAM"."MLOG$_T_TEST" purge

这样ORACLE在打扫完战场以后，就完成了在线重定义操作，根据这个实验可以看出，ORACLE的在线重定义是使用物化视图实现的，当然整个过程并没这么简单，本文只摘取了部分trace内容，有想深入研究的朋友可以仔细分析下trace文件。

本文固定链接: https://www.dbdream.com.cn/2013/06/%e5%9c%a8%e7%ba%bf%e9%87%8d%e5%ae%9a%e4%b9%89%e6%98%af%e6%80%8e%e4%b9%88%e5%ae%9e%e7%8e%b0%e7%9a%84/ | 信春哥，系统稳，闭眼上线不回滚！