首页 > 代码库 > ORACLE系统包DBMS_REDEFINITION实现表的在线重定义
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
转载请标注源文链接,否则追究法律责任!
ORACLE系统包DBMS_REDEFINITION实现表的在线重定义