1、准备测试表

SQL> create table t_d as select * from all_objects;
Table created
SQL> alter table t_d add primary key(object_id);
Table altered
SQL> create table t_d_t as select * from t_d where 1=0;
Table created
SQL> alter table t_d_t add primary key(object_id);
Table altered
SQL> select count(*) from T_D;
  COUNT(*)
----------
     48945
SQL> select count(*) from T_D_T;
  COUNT(*)
----------
     0

2、执行在线同步

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'T_D', DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T_D', 'T_D_T');
PL/SQL procedure successfully completed
 

3、查询相关物化视图

SQL> select owner,mview_name from user_mviews;
OWNER                          MVIEW_NAME
------------------------------ ------------------------------
CHF                            T_D_T
SQL> select log_owner,master,log_table from user_mview_logs;
LOG_OWNER                      MASTER                         LOG_TABLE
------------------------------ ------------------------------ ------------------------------
CHF                            T_D                            MLOG$_T_D
CREATE MATERIALIZED VIEW T_D_T
ON PREBUILT TABLE
REFRESH FAST ON DEMAND
AS
SELECT "T_D"."OWNER" "OWNER","T_D"."OBJECT_NAME" "OBJECT_NAME",
"T_D"."SUBOBJECT_NAME" "SUBOBJECT_NAME","T_D"."OBJECT_ID" "OBJECT_ID",
"T_D"."DATA_OBJECT_ID" "DATA_OBJECT_ID","T_D"."OBJECT_TYPE" "OBJECT_TYPE",
"T_D"."CREATED" "CREATED","T_D"."LAST_DDL_TIME" "LAST_DDL_TIME",
"T_D"."TIMESTAMP" "TIMESTAMP","T_D"."STATUS" "STATUS",
"T_D"."TEMPORARY" "TEMPORARY","T_D"."GENERATED" "GENERATED",
"T_D"."SECONDARY" "SECONDARY" FROM "CHF"."T_D" "T_D";

4、结束物化视图

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T_D', 'T_D_T');
PL/SQL procedure successfully completed

5、继续查询相关物化视图

SQL>  select log_owner,master,log_table from user_mview_logs;
SQL> select * from user_mviews;
SQL> select owner,mview_name from user_mviews;
OWNER                          MVIEW_NAME
------------------------------ ------------------------------
SQL> select log_owner,master,log_table from user_mview_logs;
LOG_OWNER                      MASTER                         LOG_TABLE
------------------------------ ------------------------------ ------------------------
SQL> select count(*) from T_D_T;
  COUNT(*)
----------
     48945

6、由试验可以得出
6.1)在线重定义本质就是利用物化视图刷新实现数据迁移
6.2)DBMS_REDEFINITION.START_REDEF_TABLE实现功能：
6.2.1)创建含on prebuilt table的物化视图和物化视图日志
6.2.2)实现通过物化视图刷新当前表中数据进入中间表
6.3)dbms_redefinition.sync_interim_table实现物化视图刷新在执行5.2）操作过程中变化数据
6.4)DBMS_REDEFINITION.FINISH_REDEF_TABLE将锁定原表，防止表上的DML,物化视图执行刷新，完成刷新后，将删除物化视图和对应的日志，将中间表rename成目标表

7、如果表无主键时，区别有
7.1）执行在线定义语句，具体见表在线重定义(无主键)
7.2）创建物化视图语句上

create materialized view T_D_T
on prebuilt table
refresh fast on demand
with rowid
as
select OWNER OWNER,
OBJECT_NAME OBJECT_NAME,
 SUBOBJECT_NAME SUBOBJECT_NAME,
 OBJECT_ID OBJECT_ID,
 DATA_OBJECT_ID DATA_OBJECT_ID,
 OBJECT_TYPE OBJECT_TYPE,
 CREATED CREATED,
 LAST_DDL_TIME LAST_DDL_TIME,
 TIMESTAMP TIMESTAMP,
 STATUS STATUS,
 TEMPORARY TEMPORARY,
 GENERATED GENERATED,
 SECONDARY SECONDARY
 from "CHF"."T_D"   "T_D";

1、创建测试表
SQL> CREATE TABLE t_M
2 AS
3 SELECT * FROM all_objects;
Table created
2、查询测试表中记录
SQL> select count(*) from t_m;
COUNT(*)
———-
48941
3、创建中间表
SQL> create table t_m_n as
2 select * from t_m where 1=0;
Table created
4、查询中间表记录
SQL> select count(*) from t_m_n;
COUNT(*)
———-
5、创建刷新物化视图
SQL> CREATE MATERIALIZED VIEW t_m_n
on prebuilt TABLE WITH REDUCED PRECISION
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM t_m;
Materialized view created
6、执行物化视图刷新
SQL> exec dbms_mview.refresh(‘T_M_N’);
PL/SQL procedure successfully completed
7、查询物化视图中记录数
SQL> select count(*) from t_m_n;
COUNT(*)
———-
48941
8、删除物化视图
SQL> DROP MATERIALIZED VIEW T_M_N;
Materialized view dropped
9、查询中间表中条数
SQL> select count(*) from t_m_n;
COUNT(*)
———-
48941
10、后续可能操作
1）t_m和t_m_n相互重命名，实现在线修移动表的位置、改表结构、降低高水位等操作，同（shrink）
2）和dblink结合，实现数据的跨版本迁移

1、T2表结构

SQL> desc t2
Name           Type          Nullable Default Comments
-------------- ------------- -------- ------- --------
OWNER          VARCHAR2(30)  Y
OBJECT_NAME    VARCHAR2(128) Y
SUBOBJECT_NAME VARCHAR2(30)  Y
OBJECT_ID      NUMBER        Y
DATA_OBJECT_ID NUMBER        Y
OBJECT_TYPE    VARCHAR2(19)  Y
CREATED        DATE          Y
LAST_DDL_TIME  DATE          Y
TIMESTAMP      VARCHAR2(19)  Y
STATUS         VARCHAR2(7)   Y
TEMPORARY      VARCHAR2(1)   Y
GENERATED      VARCHAR2(1)   Y
SECONDARY      VARCHAR2(1)   Y

2、创建中间表

CREATE TABLE T2_1
AS
SELECT * FROM t2 WHERE 1=0;

3、验证T2是否用于重定义（因没有主键，采用rowid实现）

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'T2', DBMS_REDEFINITION.cons_use_rowid);

4、执行表的在线重定义

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T2', 'T2_1','OWNER OWNER, OBJECT_NAME OBJECT_NAME, SUBOBJECT_NAME  SUBOBJECT_NAME, OBJECT_ID OBJECT_ID, DATA_OBJECT_ID DATA_OBJECT_ID, OBJECT_TYPE OBJECT_TYPE, CREATED CREATED, LAST_DDL_TIME LAST_DDL_TIME, TIMESTAMP TIMESTAMP, STATUS STATUS, TEMPORARY TEMPORARY, GENERATED GENERATED, SECONDARY SECONDARY',DBMS_REDEFINITION.cons_use_rowid);

说明：
1）采用单引号列出T2与T2_1表列的对应关系
2）列与列之间采用单引号分割，单引号后面要有空格
5、同步数据（可选）

exec dbms_redefinition.sync_interim_table(user, 'T2', 'T2_1');

6、执行结束在线定义过程

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T2', 'T2_1');

7、删除中间表

drop table t2_1 purge;

8、处理T2表（删除隐藏列）

SQL> select col#,name,type# from SYS.COL$ WHERE OBJ#=(select object_id from dba_objects where object_name='T2');
      COL# NAME                                TYPE#
---------- ------------------------------ ----------
         1 OWNER                                   1
         2 OBJECT_NAME                             1
         3 SUBOBJECT_NAME                          1
         4 OBJECT_ID                               2
         5 DATA_OBJECT_ID                          2
         6 OBJECT_TYPE                             1
         7 CREATED                                12
         8 LAST_DDL_TIME                          12
         9 TIMESTAMP                               1
        10 STATUS                                  1
        11 TEMPORARY                               1
        12 GENERATED                               1
        13 SECONDARY                               1
         0 SYS_C00014_11081015:39:40$              1
--发现一个多余隐藏列SYS_C00014_11081015:39:40$，我们需要删除
SQL>  alter table t2 set unused ("SYS_C00014_11081015:39:40$");
Table altered
SQL>  alter table t2 drop unused columns;
Table altered

表在线重定义（有主键）