使用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:创建中间表
除了表名称外,该中间表的形式需要与我们最终的分区表的形式一模一样,如列名,列的数据类型、分区字段,分区方法等。
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:结束在线重定义
exec dbms_redefinition.finish_redef_table(‘LIJIAMAN‘, ‘BSTEST‘, ‘BSTEST_TMP‘);
STEP6:创建index、创建约束,两种方法创建速度差不多
alter session set db_file_multiblock_read_count=128;alter table BSTEST add constraint BSPANELPROCESSDATAITEM_PKS primary key(timekey,itemname,sitename) using index local tablespace users;
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;
如果要了解在线重定义的详细用法及使用注意事项,可参阅官方文档:
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)
原文:https://www.cnblogs.com/lijiaman/p/9985346.html