使用ORACLE在线重定义将普通表改为分区表

因客户现场有一张表数据流量巨大，达到50G，查询起来较慢，遂决定进行分区表改造，测试环境进行测试，此次方法才用在线重定义方法

 

1.创建测试表

create table elan(id int primary key,name varchar2(32)); --表中需要定义主键，如果没有主键可以在后期添加

2.批量向表中插入数据

begin
     for i in 1..100000
     loop
         insert into elan.elan values(i,'x');
     end loop;
     commit;
end;
 /

查看表中的数据是否插入

SQL> select count(1) from elan;

  COUNT(1)
----------
    100000

3.创建中间表

--此次分区才用hash分区的方式

CREATE TABLE elan_new (ID NUMBER PRIMARY KEY, name varchar2(32)) PARTITION BY hash (id) 
( PARTITION PART01 TABLESPACE elan,   
  PARTITION PART02 TABLESPACE elan,   
  PARTITION PART03 TABLESPACE elan);

4.检测表是否可以执行在线重定义

QL> BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('ELAN','ELAN',DBMS_REDEFINITION.CONS_USE_PK);
END;
/  2    3    4  

PL/SQL procedure successfully completed.

如果没有定义主键会提示以下错误信息
begin dbms_redefinition.can_redef_table(user,'pft_party_profit_detail'); end;
ORA-12089: cannot online redefine table "elan"."elan" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 247
ORA-06512: at line 1

添加方法：

alter table elan add constraint pk_elan primary key(id);

5.开始执行数据迁移

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('ELAN', 'ELAN', 'ELAN_NEW');

PL/SQL procedure successfully completed.

6.如果表的数据很多，第5步的时候可能会很长，这期间系统可能会继续对表EMP进行写入或者更新数据，那么可以执行以下的语句，这样在执行最后一步的时候可以避免长时间的锁定(该过程可选可不选)

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('ELAN', 'ELAN', 'ELAN_NEW');

PL/SQL procedure successfully completed.

7.进行权限对象的迁移

SQL> DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ELAN', 'ELAN','ELAN_NEW',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/  2    3    4    5    6    7  

PL/SQL procedure successfully completed.

8.结束整个重定义

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('ELAN', 'ELAN', 'ELAN_NEW');

PL/SQL procedure successfully completed.

9.查询两端数据是否一致

SQL> select count(1) from elan.elan;

  COUNT(1)
----------
    100000

SQL> select count(1) from elan.elan_new;

  COUNT(1)
----------
    100000

10.删除中间表

SQL> select table_name, partition_name from user_tab_partitions where table_name = 'ELAN';

TABLE_NAME		       PARTITION_NAME
------------------------------ ------------------------------
ELAN			       PART01
ELAN			       PART02
ELAN			       PART03

SQL> 
SQL> 
SQL> select table_name, partition_name from user_tab_partitions where table_name = 'ELAN_NEW';

no rows selected


SQL> drop table elan_new purge;

Table dropped.

SQL> 
SQL> 
SQL> select table_name, partition_name from user_tab_partitions where table_name = 'ELAN';

TABLE_NAME		       PARTITION_NAME
------------------------------ ------------------------------
ELAN			       PART01
ELAN			       PART02
ELAN			       PART03

SQL> select table_name, partition_name from user_tab_partitions where table_name = 'ELAN_NEW';

no rows selected

SQL> select count(1) from elan_new;
select count(1) from elan_new
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

　至此，表分区改造完成