oracle在线重定义排序,Oracle在线重定义(online redefinition)--将普通表改为分区表

使用Oracle的在线重定义技术，可以将Oracle的普通表改为分区表。操作如下：

STEP1：测试表是否可以在线重定义，这里以unixdev数据库的LIJIAMAN.BSTEST为例

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('LIJIAMAN','BSTEST', DBMS_REDEFINITION.CONS_USE_PK);

如果表上没有主键，则会报错：

SQL> exec dbms_redefinition.start_redef_table('LIJIAMAN', 'BSTEST', 'BSTEST_TMP');

ORA-12089: 不能联机重新定义无主键的表 "LIJIAMAN"."BSTEST"

ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 56

ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1498

ORA-06512: 在 line 1

STEP2：创建中间表

除了表名称外，该中间表的形式需要与我们最终的分区表的形式一模一样，如列名，列的数据类型、分区字段，分区方法等。

如果要删除列，请不要在临时表的定义中包括它们。如果要添加列，则将列定义添加到临时表中。如果要修改列，请使用所需的属性在临时表中创建它。

不必使用重新定义该表的所有索引，约束，授予和触发器来创建临时表，因为在复制相关对象时，这些将在步骤5中定义。

create tableBSTEST_tmp

( TIMEKEYVARCHAR2(40),

ITEMNAMEVARCHAR2(40),

SITENAMEVARCHAR2(40),

SITEVALUEVARCHAR2(40)

)

partitionbyrange (timekey)

( partition part_201711values less than('201712010000000000000'),

partition part_201712values less than('201801010000000000000'),

partition part_201801values less than('201802010000000000000'),

partition part_201802values less than('201803010000000000000'),

partition part_201803values less than('201804010000000000000'),

partition part_201804values less than('201805010000000000000'),

partition part_201805values less than('201806010000000000000'),

partition part_201806values less than('201807010000000000000'),

partition part_201807values less than('201808010000000000000'),

partition part_201808values less than('201809010000000000000'),

partition part_201809values less than('201810010000000000000'),

partition part_201810values less than('201811010000000000000'),

partition part_201811values less than('201812010000000000000'),

partition part_201812values less than('201901010000000000000'),

partition part_201901values less than('201902010000000000000'),

partition part_201902values less than('201903010000000000000'),

partition part_201903values less than('201904010000000000000'),

partition part_201904values less than('201905010000000000000'),

partition part_201905values less than('201906010000000000000'),

partition part_201906values less than('201907010000000000000'),

partition part_201907values less than('201908010000000000000'),

partition part_201908values less than('201909010000000000000'),

partition part_201909values less than('201910010000000000000'),

partition part_201910values less than('201911010000000000000'),

partition part_201911values less than('201912010000000000000'),

partition part_201912values less than('202001010000000000000'),

partition part_202001values less than('202002010000000000000'),

partition part_202002values less than('202003010000000000000'),

partition part_202003values less than('202004010000000000000'),

partition part_202004values less than('202005010000000000000'),

partition part_maxvaluesless than(maxvalue)

);

STEP3：执行在线重定义

exec dbms_redefinition.start_redef_table('LIJIAMAN', 'BSTEST', 'BSTEST_TMP');

备注：该步骤执行时间较长，3.7GB的表执行了60s

STEP4：[可选] 将中间表的内容与数据源同步，减少执行“结束在线重定义”的时间

execute dbms_redefinition.sync_interim_table('LIJIAMAN', 'BSTEST', 'BSTEST_TMP');

STEP5：添加索引，约束，授权和触发器

使用以下两种方法之一，将要重新定义的表中的依赖对象(例如触发器，索引，实例化视图日志，授权和约束)和统计信息复制到临时表中。方法1是首选方法，因为它比较自动，但是有时您可能会选择使用方法2。方法1还使您能够将表统计信息复制到临时表中。

方法1：自动创建从属对象

使用该COPY_TABLE_DEPENDENTS过程可在临时表上自动创建相关对象。此过程还注册从属对象。注册从属对象可以使这些对象及其复制的对等对象的身份稍后在重新定义完成过程中自动交换。结果是，重新定义完成后，从属对象的名称将与原始从属对象的名称相同。

DECLAREnum_errors PLS_INTEGER;BEGINDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('LIJIAMAN', 'BSTEST', 'BSTEST_TMP',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);END;/

查看是否有错误信息：

select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

方法2：手动创建从属对象

您可以在临时表上手动创建依赖对象。

alter table BSTEST add constraint BSPANELPROCESSDATAITEM_PKS primary key(timekey,itemname,sitename) using index local tablespace users;

STEP6：结束在线重定义

exec dbms_redefinition.finish_redef_table('LIJIAMAN', 'BSTEST', 'BSTEST_TMP');

STEP7：收集统计信息

exec dbms_stats.gather_table_stats(ownname=>'LIJIAMAN', tabname=>'BSTEST', method_opt=> 'FOR ALL INDEXED COLUMNS', estimate_percent => 10, cascade=>true);

备注：该步骤执行时间较长，3.7GB的表执行了36s，如果嫌慢，可以并行收集

STEP8：确认无误之后，删除STEP2创建的中间表

drop table BSTEST_TMP;

===========================

回退操作：如果执行过程中发生错误，可以使用以下语句回退

BEGINDBMS_REDEFINITION.ABORT_REDEF_TABLE(uname=> 'LIJIAMAN',

orig_table=> 'BSTEST',

int_table=> 'BSTEST_TMP');END;/

如果要了解在线重定义的详细用法及使用注意事项，可参阅官方文档：

1. https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11677

2. How To Partition Existing Table Using DBMS_REDEFINITION (文档 ID 472449.1)