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

最新推荐文章于 2025-04-27 22:56:56 发布

原创最新推荐文章于 2025-04-27 22:56:56 发布 · 2.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#在线重定义 #DBMS_REDEFINITION

性能调优专栏收录该内容

10 篇文章

订阅专栏

本文介绍如何使用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;