使用在线重定义回收表的空闲空间一例

对于线上库中频繁dml操作的表，尤其是删除操作，如何把表的占用空间压缩，回收空闲空间就成了DBA的工作之一。

oracle的DBMS_REDEFINITION包提供的在线重定义功能可以完成表的收缩，先看一下这个包中包含的过程：

我们通过一张测试表来把整个过程演示一下，scott用户操作如下：

SQL> create table test as select * from dba_objects;        --创建测试表test

SQL> analyze table test compute statistics;                    --收集统计信息

SQL> select NUM_ROWS,BLOCKS,AVG_SPACE from user_tables where table_name='TEST';    --AVG_SPACE：块平均空闲空间 ，ptc10%,每个块留800字节给update用

  NUM_ROWS     BLOCKS  AVG_SPACE

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

        300736       4302        868

SQL> delete test where rownum <=100000;                --删除数据后再看表的数据分布情况

SQL> analyze table test compute statistics;

SQL> select NUM_ROWS,BLOCKS,AVG_SPACE from user_tables  where table_name='TEST';--块松散了，但是表的block数不变

  NUM_ROWS     BLOCKS  AVG_SPACE

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

    200736       4302       3195

--没有主键不能做在线重定义

SQL> exec DBMS_REDEFINITION.can_redef_table('SCOTT','TEST')

BEGIN DBMS_REDEFINITION.can_redef_table('SCOTT','TEST'); END;

*

ERROR at line 1:

ORA-12089: cannot online redefine table "SCOTT"."TEST" with no primary key

ORA-06512: at "SYS.DBMS_REDEFINITION", line 143

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1627

ORA-06512: at line 1

SQL> alter table test add constraint pk_test primary key (object_id);        --增加主键

SQL> exec DBMS_REDEFINITION.can_redef_table('SCOTT','TEST')

PL/SQL procedure successfully completed.

SQL> create table test1 as select * from test where 1=0;                        --创建interim table

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE('scott','TEST','TEST1')    --执行重定义

PL/SQL procedure successfully completed.

SQL> insert into test(object_id) values (555555);

SQL> select count(*) from test;

  COUNT(*)

----------

    200737

SQL> select count(*) from test1;

  COUNT(*)

----------

    200736

SQL> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('scott','TEST','TEST1')        --将过程中差异数据同步，可多次执行，无锁

PL/SQL procedure successfully completed.

SQL> insert into test(object_id) values (666666);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test;

  COUNT(*)

----------

    200738

SQL> select count(*) from test1;

  COUNT(*)

----------

    200737

SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','TEST','TEST1')        --包括同步和切换的过程，会阻塞原表的dml

SQL> select count(*) from test1;

  COUNT(*)

----------

    200738

SQL> select count(*) from test;

  COUNT(*)

----------

    200738

SQL> select NUM_ROWS,BLOCKS,AVG_SPACE from user_tables

  2  where table_name='TEST';

  NUM_ROWS     BLOCKS  AVG_SPACE

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

    200738       2944        904

SQL> select NUM_ROWS,BLOCKS,AVG_SPACE from user_tables where table_name='TEST1';

  NUM_ROWS     BLOCKS  AVG_SPACE

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

    200738       4302       3195

至此，在线重定义完成。

=====================================================
补充一下：

关于权限，调用DBMS_REDEFINITION 包需要EXECUTE_CATALOG_ROLE角色，除此之外，还需要
CREATE ANY TABLE、
ALTER ANY TABLE、
DROP ANY TABLE、
LOCK ANY TABLE和SELECT ANY TABLE的权限。

另外关于根据主键还是rowid来做在线重定义，建议用主键，都用的是物化视图的原理，抛开用rowid方式有bug不说，做完还有隐藏列要单独处理，不细说了。

线重定义还具有把普通表转为分区表，改变表的所属表空间，重新构建表结构和存储属性等功能，以后细说，希望对你有所帮助。