普通堆表在线转换为分区表

测试有一个需求，需要将一张普通堆表，转换为一张分区，用于测试分区表性能，之前曾经准备过环境，《》，但当时是重新加载数据，这次则是将已存在的表，进行这种分区转换操作，其实也可以利用导出导入，完成这一个需求，稍微有些饶了，但既然Oracle已经提供了方法，就可以用合适的方法，实现需求。

这个方法就是Online Redefinition，即在线重定义。

如下是官方文档，对于在线重定义的介绍，

Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.

When a table is redefined online, it is accessible to both queries and DML during much of the redefinition process. Typically, the table is locked in the exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition, and that is completely transparent to users. However, if there are many concurrent DML operations during redefinition, then a longer wait might be necessary before the table can be locked. Due to the wait, more changes are committed to the original table, which increases the refresh time and the amount of time that the table is locked.

Online table redefinition requires an amount of free space that is approximately equivalent to the space used by the table being redefined. More space may be required if new columns are added.

在线重定义最大的特点，就是“在线”，就是说允许结构变更同时，对该表有其他的正常操作，增删改查，原理利用了物化视图，需要有至少一倍的空间，相当于原表拷贝，原表和目标表切换操作，会有锁表，但时间可控，对用户是透明的，因此从用户的感觉上，就像魔术一样，完成了结构变更，但实际上后台封装了操作，化繁为简。

以下为操作过程，对表等信息作了漂白，真实情况下有两张表需求，一张表数据量2000万，一张表数据量4000万，主要操作半小时内可完成，生产环境配置会高一些，执行可能更快。

首先，创建中间分区表，采用时间分区，而且利用了11g的interval，避免手工建分区操作，

create table tbl_p (   id          NUMBER not null,   code     VARCHAR2(3) not null,   cdate    DATE not null ) partition by range (cdate) interval(numtoyminterval(1,'month')) (partition p0 values less than (to_date('2015-01-01', 'yyyy-mm-dd'))) tablespace tbs;

创建主键，

alter table tbl_p add constraint pk_tbl_p primary key (id);

检查原始表是否可以，执行在线重定义，

exec dbms_redefinition.can_redef_table('USER','TBL');

确认原表统计信息已经更新，

exec dbms_stats.gather_table_stats('USER', 'TBL', cascade=true);

开始在线重定义，其执行时间取决于数据量，

begin      dbms_redefinition.start_redef_table(      uname = 'USER',        orig_table = 'TBL',      int_table = 'TBL_P');      end;  /

同步原表以及目标表，

begin      dbms_redefinition.sync_interim_table(      uname = 'USER',        orig_table = 'TBL',      int_table = 'TBL_P');      end;      /

采集中间表的统计信息，

exec dbms_stats.gather_table_stats('USER', 'TBL_P', cascade=true);

完成在线重定义，

SQL begin      dbms_redefinition.finish_redef_table(      uname = 'USER',        orig_table = 'TBL',      int_table = 'TBL_P');      end;      /

可以删除中间表，

drop table tbl_p;

虽然原始表和目标表，已经完成置换，但新的表TBL上的主键名称PK_TBL_P，要是看着不爽，可以继续标准化，

alter index pk_tbl_p rename to pk_tbl;

索引放入独立表空间，

alter index pk_tbs rebuild tablespace tbs_idx;

在新的索引表空间建索引，注意用的是局部索引，

create index idx_tbl_01 on tbs (code, cdate) local tablespace tbs_idx;

采集表和索引统计信息，

exec dbms_stats.gather_table_stats(‘USER’, ‘TBS’, cascade=true);

检索新表分区的情况，可以看见，初始分区P0，其他分区，会自动来命名，SYS_P+序号，

select table_name, partition_name from user_tab_partitions where table_name=’TBS’;

TABLE_NAME      PARTITION_NAME ----------------------- ------------------------------ TBS                       P0 TBS                       SYS_P60 TBS                       SYS_P61

...

检索新表索引分区的情况，新建分区，会自动建此分区的索引，且是局部local的分区索引，

select index_name, partition_name from user_ind_partitions;      

INDEX_NAME               PARTITION_NAME ------------------------------ ------------------------------ IDX_TBS_01                 SYS_P125 IDX_TBS_01                 SYS_P126

...

至此TBL表已经从普通堆表，在线转为interval间隔分区表，创建了local局部索引，表和索引分区，可以自动创建，无需干预，实现了测试人员的需求。

对于在线重定义操作分区表，可以参考MOS以下两篇文章，

How To Partition Existing Table Using DBMS_REDEFINITION (Doc ID 472449.1)
How To Change the Partition Column Of A Partitioned Table Using DBMS_Redefinition (Doc ID 846405.1)

如果您觉得本文有帮助，欢迎关注转发：bisal的个人杂货铺，