Oracle之在线重定义功能

 在Oracle9i出现之前，你只能通过MOVE或导出和导入的方式来进行表的重定义，因此表重定义的过程可能相当漫长或者说是一个离线过程，在此期间应用程序对该表的操作将失败。除了这个，如果用exp，我们也不能保证exp的时候该表的数据没有改变（除非单用户），而imp更是一个漫长的过程。 为了解决这个问题，Oracle9i在其DBMS_REDEFINITION软件包中引入了在线重定义功能。这个特性对24*7的数据库系统来说非常重要，使用这个技术DBA可以在保持表允许DML语句的情况下修改结构，比如添加列、移动表到其他表空间、处理表的碎片等，当然了对于表的碎片处理，在10g以后，可以考虑使用shrink操作来实现，关于shrink在这里不做讨论。

在线重定义具有以下功能：

(1)修改表的存储参数；
(2)可以将表转移到其他表空间；
(3)在表上增加、修改或删除一列或是多列；
(4)增加并行查询选项；
(5)增加分区支持；
(6)修改分区结构；
(7)重建表以减少碎片；
(8)将堆表改为索引组织表或相反的操作；

在线重定义的方法

1.基于主键
2.基于ROWID。ROWID的方式不能用于索引组织表，而且重定义后会存在隐藏列M_ROW$$。
默认采用主键的方式。

在线重定义的一些限制

1.要求原始表和中间表在同一个方案下；
2.要求有2倍甚至是多于2倍的表空间空间；
3.如果使用主键重定义的方式，原始表上要有主键；

对于在线重定义的步骤，这里不再具体说明，我们通过一个实验来演示一下，下面是一个把普通表转换成分区表在线重定义的例子

一、首先创建用户tj，并授予能够完成在线重定义的权限和角色
SQL> create user tj identified by tj
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;
User created.

SQL> GRANT CREATE SESSION, CREATE ANY TABLE,ALTER ANY TABLE,
  2        DROP ANY TABLE, LOCK ANY TABLE  ,SELECT ANY TABLE,
  3        CREATE ANY INDEX,CREATE ANY TRIGGER
  4  TO TJ;
Grant succeeded.

SQL> GRANT EXECUTE_CATALOG_ROLE TO TJ;
Grant succeeded.

二、使用TJ用户登录，创建表DEMO，作为在线重定义的原始表，在表上添加主键和一个索引
SQL> conn tj/tj
Connected.

SQL> create table demo as select empno,ename,sal,deptno from scott.emp;
Table created.

SQL> set linesize 120
SQL> set pagesize 60

SQL> select * from demo;

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            6000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            1000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
14 rows selected.

SQL>  alter table demo add constraint demo_pk primary key(empno);
Table altered.

SQL> create index demo_idx on demo(ename);
Index created.

SQL> select object_id,object_name,object_type,status from user_objects;

 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77125 DEMO            TABLE               VALID
     77126 DEMO_PK         INDEX               VALID
     77127 DEMO_IDX        INDEX               VALID

三、使用CAN_REDEF_TABLE确认表是否可以做在线重定义
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('TJ', 'DEMO');
PL/SQL procedure successfully completed.

四、创建中间表，当然这是一个空表，使用START_REDEF_TABLE开始在线重定义
SQL> create table demo_tmp
  2  partition by range(deptno)
  3  (
  4   partition p1 values less than (11),
  5   partition p2 values less than (21),
  6   partition p3 values less than (31)
  7  )
  8  as
  9  select * from demo where 1=2;
Table created.

SQL> select object_id,object_name,object_type,status from user_objects;

 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77129 DEMO_TMP        TABLE               VALID
     77130 DEMO_TMP        TABLE PARTITION     VALID
     77132 DEMO_TMP        TABLE PARTITION     VALID
     77131 DEMO_TMP        TABLE PARTITION     VALID
     77127 DEMO_IDX        INDEX               VALID
     77126 DEMO_PK         INDEX               VALID
     77125 DEMO            TABLE               VALID
7 rows selected.

SQL> BEGIN
  2      DBMS_REDEFINITION.START_REDEF_TABLE('TJ', 'DEMO', 'DEMO_TMP');
  3  END;
  4  /
PL/SQL procedure successfully completed.

SQL> select object_id,object_name,object_type,status from user_objects;
 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77134 RUPD$_DEMO      TABLE               VALID
     77133 MLOG$_DEMO      TABLE               VALID
     77129 DEMO_TMP        TABLE               VALID
     77130 DEMO_TMP        TABLE PARTITION     VALID
     77132 DEMO_TMP        TABLE PARTITION     VALID
     77131 DEMO_TMP        TABLE PARTITION     VALID
     77127 DEMO_IDX        INDEX               VALID
     77126 DEMO_PK         INDEX               VALID
     77125 DEMO            TABLE               VALID
9 rows selected.

我们注意到Oracle新建了两张表RUPD$_DEMO和MLOG$_DEMO，其实Oracle在线重定义是通过物化视图的LOG来实现的。做完这一步后，在中间表中也有了相同的数据

SQL> select * from demo;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            6000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            1000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
14 rows selected.

SQL> select * from demo_tmp;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10
      7369 SMITH            6000         20
      7566 JONES            2975         20
      7788 SCOTT            1000         20
      7876 ADAMS            1100         20
      7902 FORD             3000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7844 TURNER           1500         30
      7900 JAMES             950         30
14 rows selected.

五、使用COPY_TABLE_DEPENDENTS把原始表的权限、约束、索引、物化视图LOG在中间表上创建一份
SQL> set serveroutput on
SQL> var v_err number
SQL> exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('TJ', 'DEMO', 'DEMO_TMP',  NUM_ERRORS => :V_ERR);
PL/SQL procedure successfully completed.
SQL> print v_err
     V_ERR
----------
         0

SQL> select object_id,object_name,object_type,status from user_objects;
 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77137 TMP$$_DEMO_PK0  INDEX               VALID
     77138 TMP$$_DEMO_IDX0 INDEX               VALID
     77134 RUPD$_DEMO      TABLE               VALID
     77133 MLOG$_DEMO      TABLE               VALID
     77129 DEMO_TMP        TABLE               VALID
     77130 DEMO_TMP        TABLE PARTITION     VALID
     77132 DEMO_TMP        TABLE PARTITION     VALID
     77131 DEMO_TMP        TABLE PARTITION     VALID
     77127 DEMO_IDX        INDEX               VALID
     77126 DEMO_PK         INDEX               VALID
     77125 DEMO            TABLE               VALID
11 rows selected.

SQL> select table_name,index_name,status from user_indexes where table_name='DEMO_TMP';

TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
DEMO_TMP                       TMP$$_DEMO_IDX0               VALID
DEMO_TMP                       TMP$$_DEMO_PK0                 VALID

这里我们看到，Oracle在中间表DEMO_TMP上又根据原始表DEMO建了两个索引

六、如果在线重定义的时间比较长，而在这个过程中有其他的DML语句操作在原始表上，Oracle通过SYNC_INTERIM_TABLE来做同步

SQL> insert into demo values(1000,'TOMMY',1350,10);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from demo;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            6000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            1000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
      1000 TOMMY            1350         10
15 rows selected.

SQL> select * from demo_tmp;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10
      7369 SMITH            6000         20
      7566 JONES            2975         20
      7788 SCOTT            1000         20
      7876 ADAMS            1100         20
      7902 FORD             3000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7844 TURNER           1500         30
      7900 JAMES             950         30
14 rows selected.

上面插入一条记录到原始表DEMO中，中间表上是看不到的，这个操作会被记录在MLOG$_DEMO中，需要我们主动同步到DEMO_TMP中

SQL> desc MLOG$_DEMO
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 EMPNO                                                                      NUMBER(4)
 DMLTYPE$$                                                                  VARCHAR2(1)
 OLD_NEW$$                                                                  VARCHAR2(1)
 CHANGE_VECTOR$$                                                            RAW(255)
 XID$$                                                                      NUMBER

SQL> select empno,DMLTYPE$$,OLD_NEW$$ from MLOG$_DEMO;
     EMPNO D O
---------- - -
      1000 I N

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TJ', 'DEMO', 'DEMO_TMP');
PL/SQL procedure successfully completed.

SQL> select * from demo;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            6000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            1000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
      1000 TOMMY            1350         10
15 rows selected.

SQL> select * from demo_tmp;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10
      1000 TOMMY            1350         10
      7369 SMITH            6000         20
      7566 JONES            2975         20
      7788 SCOTT            1000         20
      7876 ADAMS            1100         20
      7902 FORD             3000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7844 TURNER           1500         30
      7900 JAMES             950         30
15 rows selected.

SQL> select empno,DMLTYPE$$,OLD_NEW$$ from MLOG$_DEMO;
no rows selected

严格意义上来说，第六步不是必须的，当做第七步的时候，Oracle会自动同步数据，不过这样会加长表不可用的时间，所以还是建议我们单独做

七、完成在线重定义，在这一步中，要对原始表DEMO以独占的方式锁定。
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('TJ', 'DEMO', 'DEMO_TMP');
PL/SQL procedure successfully completed.

SQL> select object_id,object_name,object_type,status from user_objects;

 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77129 DEMO            TABLE               VALID
     77130 DEMO            TABLE PARTITION     VALID
     77131 DEMO            TABLE PARTITION     VALID
     77132 DEMO            TABLE PARTITION     VALID
     77125 DEMO_TMP        TABLE               VALID
     77138 DEMO_IDX        INDEX               VALID
     77127 TMP$$_DEMO_IDX0 INDEX               VALID
     77137 DEMO_PK         INDEX               VALID
     77126 TMP$$_DEMO_PK0  INDEX               VALID
9 rows selected.

操作完成后，我们发现RUPD$_DEMO和MLOG$_DEMO被自动删除，另外我们也可以看到重定义的效果了

SQL> SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'DEMO';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
DEMO                           P1
DEMO                           P2
DEMO                           P3

SQL> select table_name,index_name,status from user_indexes where table_name='DEMO';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
DEMO                           DEMO_IDX                       VALID
DEMO                           DEMO_PK                        VALID

SQL> select table_name,index_name,status from user_indexes where table_name='DEMO_TMP';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
DEMO_TMP                       TMP$$_DEMO_IDX0               VALID
DEMO_TMP                       TMP$$_DEMO_PK0                 VALID

SQL> select * from demo partition(p1);
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10
      1000 TOMMY            1350         10

本文根据Oracle文档整理而来，并部分参考了网络上的文章。

 在Oracle9i出现之前，你只能通过MOVE或导出和导入的方式来进行表的重定义，因此表重定义的过程可能相当漫长或者说是一个离线过程，在此期间应用程序对该表的操作将失败。除了这个，如果用exp，我们也不能保证exp的时候该表的数据没有改变（除非单用户），而imp更是一个漫长的过程。 为了解决这个问题，Oracle9i在其DBMS_REDEFINITION软件包中引入了在线重定义功能。这个特性对24*7的数据库系统来说非常重要，使用这个技术DBA可以在保持表允许DML语句的情况下修改结构，比如添加列、移动表到其他表空间、处理表的碎片等，当然了对于表的碎片处理，在10g以后，可以考虑使用shrink操作来实现，关于shrink在这里不做讨论。

在线重定义具有以下功能：

(1)修改表的存储参数；
(2)可以将表转移到其他表空间；
(3)在表上增加、修改或删除一列或是多列；
(4)增加并行查询选项；
(5)增加分区支持；
(6)修改分区结构；
(7)重建表以减少碎片；
(8)将堆表改为索引组织表或相反的操作；

在线重定义的方法

1.基于主键
2.基于ROWID。ROWID的方式不能用于索引组织表，而且重定义后会存在隐藏列M_ROW$$。
默认采用主键的方式。

在线重定义的一些限制

1.要求原始表和中间表在同一个方案下；
2.要求有2倍甚至是多于2倍的表空间空间；
3.如果使用主键重定义的方式，原始表上要有主键；

对于在线重定义的步骤，这里不再具体说明，我们通过一个实验来演示一下，下面是一个把普通表转换成分区表在线重定义的例子

一、首先创建用户tj，并授予能够完成在线重定义的权限和角色
SQL> create user tj identified by tj
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;
User created.

SQL> GRANT CREATE SESSION, CREATE ANY TABLE,ALTER ANY TABLE,
  2        DROP ANY TABLE, LOCK ANY TABLE  ,SELECT ANY TABLE,
  3        CREATE ANY INDEX,CREATE ANY TRIGGER
  4  TO TJ;
Grant succeeded.

SQL> GRANT EXECUTE_CATALOG_ROLE TO TJ;
Grant succeeded.

二、使用TJ用户登录，创建表DEMO，作为在线重定义的原始表，在表上添加主键和一个索引
SQL> conn tj/tj
Connected.

SQL> create table demo as select empno,ename,sal,deptno from scott.emp;
Table created.

SQL> set linesize 120
SQL> set pagesize 60

SQL> select * from demo;

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            6000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            1000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
14 rows selected.

SQL>  alter table demo add constraint demo_pk primary key(empno);
Table altered.

SQL> create index demo_idx on demo(ename);
Index created.

SQL> select object_id,object_name,object_type,status from user_objects;

 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77125 DEMO            TABLE               VALID
     77126 DEMO_PK         INDEX               VALID
     77127 DEMO_IDX        INDEX               VALID

三、使用CAN_REDEF_TABLE确认表是否可以做在线重定义
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('TJ', 'DEMO');
PL/SQL procedure successfully completed.

四、创建中间表，当然这是一个空表，使用START_REDEF_TABLE开始在线重定义
SQL> create table demo_tmp
  2  partition by range(deptno)
  3  (
  4   partition p1 values less than (11),
  5   partition p2 values less than (21),
  6   partition p3 values less than (31)
  7  )
  8  as
  9  select * from demo where 1=2;
Table created.

SQL> select object_id,object_name,object_type,status from user_objects;

 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77129 DEMO_TMP        TABLE               VALID
     77130 DEMO_TMP        TABLE PARTITION     VALID
     77132 DEMO_TMP        TABLE PARTITION     VALID
     77131 DEMO_TMP        TABLE PARTITION     VALID
     77127 DEMO_IDX        INDEX               VALID
     77126 DEMO_PK         INDEX               VALID
     77125 DEMO            TABLE               VALID
7 rows selected.

SQL> BEGIN
  2      DBMS_REDEFINITION.START_REDEF_TABLE('TJ', 'DEMO', 'DEMO_TMP');
  3  END;
  4  /
PL/SQL procedure successfully completed.

SQL> select object_id,object_name,object_type,status from user_objects;
 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77134 RUPD$_DEMO      TABLE               VALID
     77133 MLOG$_DEMO      TABLE               VALID
     77129 DEMO_TMP        TABLE               VALID
     77130 DEMO_TMP        TABLE PARTITION     VALID
     77132 DEMO_TMP        TABLE PARTITION     VALID
     77131 DEMO_TMP        TABLE PARTITION     VALID
     77127 DEMO_IDX        INDEX               VALID
     77126 DEMO_PK         INDEX               VALID
     77125 DEMO            TABLE               VALID
9 rows selected.

我们注意到Oracle新建了两张表RUPD$_DEMO和MLOG$_DEMO，其实Oracle在线重定义是通过物化视图的LOG来实现的。做完这一步后，在中间表中也有了相同的数据

SQL> select * from demo;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            6000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            1000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
14 rows selected.

SQL> select * from demo_tmp;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10
      7369 SMITH            6000         20
      7566 JONES            2975         20
      7788 SCOTT            1000         20
      7876 ADAMS            1100         20
      7902 FORD             3000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7844 TURNER           1500         30
      7900 JAMES             950         30
14 rows selected.

五、使用COPY_TABLE_DEPENDENTS把原始表的权限、约束、索引、物化视图LOG在中间表上创建一份
SQL> set serveroutput on
SQL> var v_err number
SQL> exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('TJ', 'DEMO', 'DEMO_TMP',  NUM_ERRORS => :V_ERR);
PL/SQL procedure successfully completed.
SQL> print v_err
     V_ERR
----------
         0

SQL> select object_id,object_name,object_type,status from user_objects;
 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77137 TMP$$_DEMO_PK0  INDEX               VALID
     77138 TMP$$_DEMO_IDX0 INDEX               VALID
     77134 RUPD$_DEMO      TABLE               VALID
     77133 MLOG$_DEMO      TABLE               VALID
     77129 DEMO_TMP        TABLE               VALID
     77130 DEMO_TMP        TABLE PARTITION     VALID
     77132 DEMO_TMP        TABLE PARTITION     VALID
     77131 DEMO_TMP        TABLE PARTITION     VALID
     77127 DEMO_IDX        INDEX               VALID
     77126 DEMO_PK         INDEX               VALID
     77125 DEMO            TABLE               VALID
11 rows selected.

SQL> select table_name,index_name,status from user_indexes where table_name='DEMO_TMP';

TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
DEMO_TMP                       TMP$$_DEMO_IDX0               VALID
DEMO_TMP                       TMP$$_DEMO_PK0                 VALID

这里我们看到，Oracle在中间表DEMO_TMP上又根据原始表DEMO建了两个索引

六、如果在线重定义的时间比较长，而在这个过程中有其他的DML语句操作在原始表上，Oracle通过SYNC_INTERIM_TABLE来做同步

SQL> insert into demo values(1000,'TOMMY',1350,10);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from demo;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            6000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            1000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
      1000 TOMMY            1350         10
15 rows selected.

SQL> select * from demo_tmp;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10
      7369 SMITH            6000         20
      7566 JONES            2975         20
      7788 SCOTT            1000         20
      7876 ADAMS            1100         20
      7902 FORD             3000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7844 TURNER           1500         30
      7900 JAMES             950         30
14 rows selected.

上面插入一条记录到原始表DEMO中，中间表上是看不到的，这个操作会被记录在MLOG$_DEMO中，需要我们主动同步到DEMO_TMP中

SQL> desc MLOG$_DEMO
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 EMPNO                                                                      NUMBER(4)
 DMLTYPE$$                                                                  VARCHAR2(1)
 OLD_NEW$$                                                                  VARCHAR2(1)
 CHANGE_VECTOR$$                                                            RAW(255)
 XID$$                                                                      NUMBER

SQL> select empno,DMLTYPE$$,OLD_NEW$$ from MLOG$_DEMO;
     EMPNO D O
---------- - -
      1000 I N

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TJ', 'DEMO', 'DEMO_TMP');
PL/SQL procedure successfully completed.

SQL> select * from demo;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            6000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            1000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
      1000 TOMMY            1350         10
15 rows selected.

SQL> select * from demo_tmp;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10
      1000 TOMMY            1350         10
      7369 SMITH            6000         20
      7566 JONES            2975         20
      7788 SCOTT            1000         20
      7876 ADAMS            1100         20
      7902 FORD             3000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7844 TURNER           1500         30
      7900 JAMES             950         30
15 rows selected.

SQL> select empno,DMLTYPE$$,OLD_NEW$$ from MLOG$_DEMO;
no rows selected

严格意义上来说，第六步不是必须的，当做第七步的时候，Oracle会自动同步数据，不过这样会加长表不可用的时间，所以还是建议我们单独做

七、完成在线重定义，在这一步中，要对原始表DEMO以独占的方式锁定。
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('TJ', 'DEMO', 'DEMO_TMP');
PL/SQL procedure successfully completed.

SQL> select object_id,object_name,object_type,status from user_objects;

 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77129 DEMO            TABLE               VALID
     77130 DEMO            TABLE PARTITION     VALID
     77131 DEMO            TABLE PARTITION     VALID
     77132 DEMO            TABLE PARTITION     VALID
     77125 DEMO_TMP        TABLE               VALID
     77138 DEMO_IDX        INDEX               VALID
     77127 TMP$$_DEMO_IDX0 INDEX               VALID
     77137 DEMO_PK         INDEX               VALID
     77126 TMP$$_DEMO_PK0  INDEX               VALID
9 rows selected.

操作完成后，我们发现RUPD$_DEMO和MLOG$_DEMO被自动删除，另外我们也可以看到重定义的效果了

SQL> SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'DEMO';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
DEMO                           P1
DEMO                           P2
DEMO                           P3

SQL> select table_name,index_name,status from user_indexes where table_name='DEMO';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
DEMO                           DEMO_IDX                       VALID
DEMO                           DEMO_PK                        VALID

SQL> select table_name,index_name,status from user_indexes where table_name='DEMO_TMP';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
DEMO_TMP                       TMP$$_DEMO_IDX0               VALID
DEMO_TMP                       TMP$$_DEMO_PK0                 VALID

SQL> select * from demo partition(p1);
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10
      1000 TOMMY            1350         10

本文根据Oracle文档整理而来，并部分参考了网络上的文章。

 在Oracle9i出现之前，你只能通过MOVE或导出和导入的方式来进行表的重定义，因此表重定义的过程可能相当漫长或者说是一个离线过程，在此期间应用程序对该表的操作将失败。除了这个，如果用exp，我们也不能保证exp的时候该表的数据没有改变（除非单用户），而imp更是一个漫长的过程。 为了解决这个问题，Oracle9i在其DBMS_REDEFINITION软件包中引入了在线重定义功能。这个特性对24*7的数据库系统来说非常重要，使用这个技术DBA可以在保持表允许DML语句的情况下修改结构，比如添加列、移动表到其他表空间、处理表的碎片等，当然了对于表的碎片处理，在10g以后，可以考虑使用shrink操作来实现，关于shrink在这里不做讨论。

在线重定义具有以下功能：

(1)修改表的存储参数；
(2)可以将表转移到其他表空间；
(3)在表上增加、修改或删除一列或是多列；
(4)增加并行查询选项；
(5)增加分区支持；
(6)修改分区结构；
(7)重建表以减少碎片；
(8)将堆表改为索引组织表或相反的操作；

在线重定义的方法

1.基于主键
2.基于ROWID。ROWID的方式不能用于索引组织表，而且重定义后会存在隐藏列M_ROW$$。
默认采用主键的方式。

在线重定义的一些限制

1.要求原始表和中间表在同一个方案下；
2.要求有2倍甚至是多于2倍的表空间空间；
3.如果使用主键重定义的方式，原始表上要有主键；

对于在线重定义的步骤，这里不再具体说明，我们通过一个实验来演示一下，下面是一个把普通表转换成分区表在线重定义的例子

一、首先创建用户tj，并授予能够完成在线重定义的权限和角色
SQL> create user tj identified by tj
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;
User created.

SQL> GRANT CREATE SESSION, CREATE ANY TABLE,ALTER ANY TABLE,
  2        DROP ANY TABLE, LOCK ANY TABLE  ,SELECT ANY TABLE,
  3        CREATE ANY INDEX,CREATE ANY TRIGGER
  4  TO TJ;
Grant succeeded.

SQL> GRANT EXECUTE_CATALOG_ROLE TO TJ;
Grant succeeded.

二、使用TJ用户登录，创建表DEMO，作为在线重定义的原始表，在表上添加主键和一个索引
SQL> conn tj/tj
Connected.

SQL> create table demo as select empno,ename,sal,deptno from scott.emp;
Table created.

SQL> set linesize 120
SQL> set pagesize 60

SQL> select * from demo;

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            6000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            1000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
14 rows selected.

SQL>  alter table demo add constraint demo_pk primary key(empno);
Table altered.

SQL> create index demo_idx on demo(ename);
Index created.

SQL> select object_id,object_name,object_type,status from user_objects;

 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77125 DEMO            TABLE               VALID
     77126 DEMO_PK         INDEX               VALID
     77127 DEMO_IDX        INDEX               VALID

三、使用CAN_REDEF_TABLE确认表是否可以做在线重定义
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('TJ', 'DEMO');
PL/SQL procedure successfully completed.

四、创建中间表，当然这是一个空表，使用START_REDEF_TABLE开始在线重定义
SQL> create table demo_tmp
  2  partition by range(deptno)
  3  (
  4   partition p1 values less than (11),
  5   partition p2 values less than (21),
  6   partition p3 values less than (31)
  7  )
  8  as
  9  select * from demo where 1=2;
Table created.

SQL> select object_id,object_name,object_type,status from user_objects;

 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77129 DEMO_TMP        TABLE               VALID
     77130 DEMO_TMP        TABLE PARTITION     VALID
     77132 DEMO_TMP        TABLE PARTITION     VALID
     77131 DEMO_TMP        TABLE PARTITION     VALID
     77127 DEMO_IDX        INDEX               VALID
     77126 DEMO_PK         INDEX               VALID
     77125 DEMO            TABLE               VALID
7 rows selected.

SQL> BEGIN
  2      DBMS_REDEFINITION.START_REDEF_TABLE('TJ', 'DEMO', 'DEMO_TMP');
  3  END;
  4  /
PL/SQL procedure successfully completed.

SQL> select object_id,object_name,object_type,status from user_objects;
 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77134 RUPD$_DEMO      TABLE               VALID
     77133 MLOG$_DEMO      TABLE               VALID
     77129 DEMO_TMP        TABLE               VALID
     77130 DEMO_TMP        TABLE PARTITION     VALID
     77132 DEMO_TMP        TABLE PARTITION     VALID
     77131 DEMO_TMP        TABLE PARTITION     VALID
     77127 DEMO_IDX        INDEX               VALID
     77126 DEMO_PK         INDEX               VALID
     77125 DEMO            TABLE               VALID
9 rows selected.

我们注意到Oracle新建了两张表RUPD$_DEMO和MLOG$_DEMO，其实Oracle在线重定义是通过物化视图的LOG来实现的。做完这一步后，在中间表中也有了相同的数据

SQL> select * from demo;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            6000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            1000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
14 rows selected.

SQL> select * from demo_tmp;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10
      7369 SMITH            6000         20
      7566 JONES            2975         20
      7788 SCOTT            1000         20
      7876 ADAMS            1100         20
      7902 FORD             3000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7844 TURNER           1500         30
      7900 JAMES             950         30
14 rows selected.

五、使用COPY_TABLE_DEPENDENTS把原始表的权限、约束、索引、物化视图LOG在中间表上创建一份
SQL> set serveroutput on
SQL> var v_err number
SQL> exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('TJ', 'DEMO', 'DEMO_TMP',  NUM_ERRORS => :V_ERR);
PL/SQL procedure successfully completed.
SQL> print v_err
     V_ERR
----------
         0

SQL> select object_id,object_name,object_type,status from user_objects;
 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77137 TMP$$_DEMO_PK0  INDEX               VALID
     77138 TMP$$_DEMO_IDX0 INDEX               VALID
     77134 RUPD$_DEMO      TABLE               VALID
     77133 MLOG$_DEMO      TABLE               VALID
     77129 DEMO_TMP        TABLE               VALID
     77130 DEMO_TMP        TABLE PARTITION     VALID
     77132 DEMO_TMP        TABLE PARTITION     VALID
     77131 DEMO_TMP        TABLE PARTITION     VALID
     77127 DEMO_IDX        INDEX               VALID
     77126 DEMO_PK         INDEX               VALID
     77125 DEMO            TABLE               VALID
11 rows selected.

SQL> select table_name,index_name,status from user_indexes where table_name='DEMO_TMP';

TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
DEMO_TMP                       TMP$$_DEMO_IDX0               VALID
DEMO_TMP                       TMP$$_DEMO_PK0                 VALID

这里我们看到，Oracle在中间表DEMO_TMP上又根据原始表DEMO建了两个索引

六、如果在线重定义的时间比较长，而在这个过程中有其他的DML语句操作在原始表上，Oracle通过SYNC_INTERIM_TABLE来做同步

SQL> insert into demo values(1000,'TOMMY',1350,10);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from demo;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            6000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            1000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
      1000 TOMMY            1350         10
15 rows selected.

SQL> select * from demo_tmp;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10
      7369 SMITH            6000         20
      7566 JONES            2975         20
      7788 SCOTT            1000         20
      7876 ADAMS            1100         20
      7902 FORD             3000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7844 TURNER           1500         30
      7900 JAMES             950         30
14 rows selected.

上面插入一条记录到原始表DEMO中，中间表上是看不到的，这个操作会被记录在MLOG$_DEMO中，需要我们主动同步到DEMO_TMP中

SQL> desc MLOG$_DEMO
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 EMPNO                                                                      NUMBER(4)
 DMLTYPE$$                                                                  VARCHAR2(1)
 OLD_NEW$$                                                                  VARCHAR2(1)
 CHANGE_VECTOR$$                                                            RAW(255)
 XID$$                                                                      NUMBER

SQL> select empno,DMLTYPE$$,OLD_NEW$$ from MLOG$_DEMO;
     EMPNO D O
---------- - -
      1000 I N

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TJ', 'DEMO', 'DEMO_TMP');
PL/SQL procedure successfully completed.

SQL> select * from demo;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7369 SMITH            6000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7566 JONES            2975         20
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7782 CLARK            2450         10
      7788 SCOTT            1000         20
      7839 KING             5000         10
      7844 TURNER           1500         30
      7876 ADAMS            1100         20
      7900 JAMES             950         30
      7902 FORD             3000         20
      7934 MILLER           1300         10
      1000 TOMMY            1350         10
15 rows selected.

SQL> select * from demo_tmp;
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10
      1000 TOMMY            1350         10
      7369 SMITH            6000         20
      7566 JONES            2975         20
      7788 SCOTT            1000         20
      7876 ADAMS            1100         20
      7902 FORD             3000         20
      7499 ALLEN            1600         30
      7521 WARD             1250         30
      7654 MARTIN           1250         30
      7698 BLAKE            2850         30
      7844 TURNER           1500         30
      7900 JAMES             950         30
15 rows selected.

SQL> select empno,DMLTYPE$$,OLD_NEW$$ from MLOG$_DEMO;
no rows selected

严格意义上来说，第六步不是必须的，当做第七步的时候，Oracle会自动同步数据，不过这样会加长表不可用的时间，所以还是建议我们单独做

七、完成在线重定义，在这一步中，要对原始表DEMO以独占的方式锁定。
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('TJ', 'DEMO', 'DEMO_TMP');
PL/SQL procedure successfully completed.

SQL> select object_id,object_name,object_type,status from user_objects;

 OBJECT_ID OBJECT_NAME     OBJECT_TYPE         STATUS
---------- --------------- ------------------- -------
     77129 DEMO            TABLE               VALID
     77130 DEMO            TABLE PARTITION     VALID
     77131 DEMO            TABLE PARTITION     VALID
     77132 DEMO            TABLE PARTITION     VALID
     77125 DEMO_TMP        TABLE               VALID
     77138 DEMO_IDX        INDEX               VALID
     77127 TMP$$_DEMO_IDX0 INDEX               VALID
     77137 DEMO_PK         INDEX               VALID
     77126 TMP$$_DEMO_PK0  INDEX               VALID
9 rows selected.

操作完成后，我们发现RUPD$_DEMO和MLOG$_DEMO被自动删除，另外我们也可以看到重定义的效果了

SQL> SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'DEMO';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
DEMO                           P1
DEMO                           P2
DEMO                           P3

SQL> select table_name,index_name,status from user_indexes where table_name='DEMO';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
DEMO                           DEMO_IDX                       VALID
DEMO                           DEMO_PK                        VALID

SQL> select table_name,index_name,status from user_indexes where table_name='DEMO_TMP';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
DEMO_TMP                       TMP$$_DEMO_IDX0               VALID
DEMO_TMP                       TMP$$_DEMO_PK0                 VALID

SQL> select * from demo partition(p1);
     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7782 CLARK            2450         10
      7839 KING             5000         10
      7934 MILLER           1300         10
      1000 TOMMY            1350         10

本文根据Oracle文档整理而来，并部分参考了网络上的文章。