ORACLE系统包DBMS_REDEFINITION实现表的在线重定义

        ORACLE自带的DBMS_REDEFINITION包功能非常强大，可以用于降低高水位线，回收碎片空间，对表做在线重定义（添加或删除字段，改变字段类型，普通表重定义表为分区表，分区表重定义表为普通表等等）

        另外，它有一个非常强大的功能，“在线”二字体现得淋漓尽致，但是也并不是完全的在线，因为在完成重定义前的最后一下，会持会表级排他锁，但这个锁定时间是可控的。

        如果你也曾为delete无法降低高水位线而烦恼或者由于历史表太大导致维护困难，我相信这篇文章将对你有很大的感触，因此相信绝大朋友都用过诸如exp/imp，EXPDP/IMPDP，SHINK SPACE,MOVE等释放碎片，但业务影响范围非常大，相比之下DBMS_REDEFINITION的方式就有太多的优势了！

 

以下，拿普通表在线重定义成分区表为例，演示历史表过大导致维护困难：

 

SQL> list
  1* create table ori_tab tablespace users as select * from ab
SQL> select count(*) from ori_tab;

  COUNT(*)
----------
       100

SQL> SQL> ed
Wrote file afiedt.buf

  1  begin
  2    dbms_stats.gather_table_stats( 'T1','ORI_TAB' );
  3* end;
SQL> /

PL/SQL procedure successfully completed.
SQL> select blocks,empty_blocks from user_tables where table_name='ORI_TAB';

    BLOCKS EMPTY_BLOCKS
---------- ------------
  4       0               <<<<占用4个数据块

SQL>  delete from ori_tab where a > 50;

50 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) from ori_tab;

COUNT(DISTINCTDBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID))
---------------------------------------------------
        1        <<<<<<<<<实际数据只占用1个数据块,说明delete操作并没有回收高水位线

SQL> alter table ori_tab add constraint oritab_a_pk primary key (a);    ---为表创建主键,为了在线重定做准备(重定义可以有两种方式rowid或主键_not NULL)

Table altered.

+++1检查ORI_TAB是否支持在线重定义
SQL> exec dbms_redefinition.can_redef_table(uname => 'T1',tname => 'ORI_TAB');

PL/SQL procedure successfully completed.

没有报错,说明当前表支持在线重定义

+++2创建新表INT_TAB
SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE int_tab
  2  ( a number
  3  , b varchar2(2)
  4  )
  5    PARTITION BY RANGE (a)
  6  ( PARTITION p0 VALUES LESS THAN (10),
  7    PARTITION p1 VALUES LESS THAN (20),
  8    PARTITION p2 VALUES LESS THAN (30),
  9  PARTITION p3 VALUES LESS THAN (40),
 10*  PARTITION p4 values less than (maxvalue))
SQL> /

Table created.

+++3 开始做
SQL> alter session force parallel dml parallel 4;

Session altered.

SQL> alter session force parallel query parallel 4;

Session altered.

SQL> exec dbms_redefinition.start_redef_table(uname => 'T1',orig_table => 'ORI_TAB',int_table => 'INT_TAB',col_mapping => NULL,options_flag => DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM INT_TAB;

  COUNT(*)
----------
 50       <<<<<<<已经看到数据被复制过去了

SQL> select index_name from user_indexes where table_name='INT_TAB';

no rows selected               <<<<<<<<<<<<索引还未自动创建

SQL> change/INT_TAB/ORI_TAB
  1* select index_name from user_indexes where table_name='ORI_TAB'
SQL> /

INDEX_NAME
--------------------------------------------------------------------------------
ORITAB_A_PK

+++4 COPY相关对象（索引，触发器，视图等）

SQL> INSERT INTO ORI_TAB VALUES(51,'NW');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM ORI_TAB;

  COUNT(*)
----------
 51              <<<<源本是51条记录

SQL> SELECT COUNT(*) FROM INT_TAB;

  COUNT(*)
----------
 50              <<<<新表依旧是50条记录

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2    num_errors PLS_INTEGER;
  3  BEGIN
  4    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('T1',
  5            'ORI_TAB',
  6            'INT_TAB',
  7            DBMS_REDEFINITION.CONS_ORIG_PARAMS,
  8            TRUE,
  9            TRUE,
 10            TRUE,
 11            TRUE,
 12            num_errors);
 13* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM ORI_TAB;

  COUNT(*)
----------
 51

SQL> SELECT COUNT(*) FROM INT_TAB;

  COUNT(*)
----------
 50

SQL> select index_name from user_indexes where table_name='INT_TAB';

INDEX_NAME
--------------------------------------------------------------------------------
TMP$$_ORITAB_A_PK0                   <<<<索引已经被自动创建

+++5 源表与新表做同步
SQL> exec dbms_redefinition.sync_interim_table('T1','ORI_TAB','INT_TAB');

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM ORI_TAB;

  COUNT(*)
----------
 51

SQL> SELECT COUNT(*) FROM INT_TAB;

  COUNT(*)
----------
 51                 <<<<<说明如果源表被更新频繁，我们也可以通过该方法使源表与新表尽可能多地进行数据同步
 
 
注：SYNC的程序决定了下面一步FINSH的时间长短，因为在线重定义方法在最后一步会对表持有表级排他锁。

+++6 完成在线重定义
SQL> exec dbms_redefinition.finish_redef_table('T1','ORI_TAB','INT_TAB');

PL/SQL procedure successfully completed.

另一个窗口查询源表ORI_TAB：
SQL> /

  COUNT(*)
----------
 51

SQL> /
/
/                 <<<<<<<此时持有表级排他锁

  COUNT(*)
----------
 51

SQL>
  COUNT(*)
----------
 51

SQL>
  COUNT(*)
----------
 51

 

至此，表的在线重定义就算完成了，原理就是通过一个临时段对旧数据进行同步，最终将旧数据段切换为生产。因此该方法对可用空间有要求，需要额外的空间进行数据同步并保存。

 

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

本文来自于我的技术博客 http://blog.csdn.net/robo23

转载请标注源文链接,否则追究法律责任!