Oracle普通表转成分区表（在线重定义）

oracle官方建议当表的大小大于2GB的时候就使用分区表进行管理，分区表相对于小的表管理和性能上都有很大的优势，本文档暂时不介绍具体的优势，主要介绍几种普通表转换成分区表的方法；
【方法概述】oracle官方给了以下四种操作的方法：
 A)  Export/import method（导入导出）
 B)  Insert with a subquery method（插入子查询的方法）
 C)  Partition exchange method（交换分区法）
 D)  DBMS_REDEFINITION（在线重定义）
这些方法的思路都是创建一个新的分区表，然后把旧表的数据转移到新表上面，接着转移相应的依赖关系，最后进行表的重命名，把新表和旧表rename。
其中A、B、C这三种方法都会影响到系统的正常使用，本文档不做详细的介绍，本文档主要介绍D方法，这种方法是目前普遍在进行普通表转换成分区表的方法。
【在线重定义进行分区表的操作】整个操作的思路如下，以LIUSHIMING用户下的bigtable表为例

在线重定义的大致操作流程如下：
       （1）创建基础表A，如果存在，就不需要操作。
       （2）创建临时的分区表B。
       （3）开始重定义，将基表A的数据导入临时分区表B。
       （4）结束重定义，此时在DB的 Name Directory里，已经将2个表进行了交换。即此时基表A成了分区表，我们创建的临时分区表B 成了普通表。 此时我们可以删除我们创建的临时表B。它已经是普通表。

SQL> create tablespace liushiming datafile '/dat/DBData/oradata/NUODA/liushiming01.dbf' size 1g autoextend on next 10m maxsize unlimited;
Tablespace created.

SQL> create user liushiming identified by liushiming default tablespace liushiming;
User created.

SQL> grant dba to liushiming;
Grant succeeded.

SQL> conn liushiming/liushiming
Connected.

SQL> create table bigtable (id number(10),name varchar2(100));
Table created.

SQL> begin
  for i in 1..999999 loop
  insert into bigtable values(i,'test'||i);
    end loop;
    end;  
/

SQL> alter table bigtable add constraint pk_id primary key(id);
Table altered.

1. 先确认下表能不能进行分区
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('LIUSHIMING','BIGTABLE',1);
END;
/
PL/SQL procedure successfully completed. 显示的是没有问题的

2.进行临时表的创建，以ID作为分区的选项
CREATE TABLE BIGTABLEBACKUP
(
  ID NUMBER(10),
  NAME VARCHAR2(100)
)
PARTITION BY RANGE (ID)
(
  PARTITION T1 VALUES LESS THAN (200000),
  PARTITION T2 VALUES LESS THAN (600000),
  PARTITION T3 VALUES LESS THAN (1000000)
      )

3.开始执行数据的迁移
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('LIUSHIMING', 'BIGTABLE', 'BIGTABLEBACKUP');
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('LIUSHIMING', 'BIGTABLE', 'BIGTABLEBACKUP'); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 1
SQL> select count(*) from bigtable;

  COUNT(*)
----------
   1000000由此看出表里有1000000，分区最大值是小于1000000的，故删除1000000这个数；
SQL> delete from bigtable where id=1000000;
1 row deleted.
SQL> commit;
Commit complete.
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('LIUSHIMING', 'BIGTABLE', 'BIGTABLEBACKUP');
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('LIUSHIMING', 'BIGTABLE', 'BIGTABLEBACKUP'); END;
*
ERROR at line 1:
ORA-12091: cannot online redefine table "LIUSHIMING"."BIGTABLE" with
materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 50
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: at line 1
看了metalink后才发现原来是对在线重定义的原理不了解，在线重定义是通过物化视图进行更新，重定义完成后会执行DBMS_REDEFINITION.FINISH_REDEF_TABLE来删除物化视图和变更日志。由于在我在本地不断的测试，忘记清理了，所以最终的解决方法是：
SQL> drop snapshot log on bigtable;   
SQL> drop materialized view bigtablebackup;
中断操作
SQL> execute dbms_redefinition.abort_redef_table('LIUSHIMING', 'BIGTABLE', 'BIGTABLEBACKUP');
PL/SQL procedure successfully completed.
)
重新开始迁移：
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('LIUSHIMING', 'BIGTABLE', 'BIGTABLEBACKUP');
PL/SQL procedure successfully completed.

4.如果表的数据很多，3步的时候可能会很长，这期间系统可能会继续对表BIGTABLE进行写入或者更新数据，那么可以执行以下的语句，这样在执行最后一步的时候可以避免长时间的锁定(该过程可选可不选)
SQL> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('LIUSHIMING', 'BIGTABLE', 'BIGTABLEBACKUP');
PL/SQL procedure successfully completed.

5.进行权限对象的迁移
DECLARE 
num_errors PLS_INTEGER; 
BEGIN 
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('LIUSHIMING','BIGTABLE','BIGTABLEBACKUP', 
  DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors); 
END; 
/ 
PL/SQL procedure successfully completed.

6.查询相关错误，在操作之前先检查，查询DBA_REDEFINITION_ERRORS试图查询错误：
select object_name, base_table_name, ddl_txt from   DBA_REDEFINITION_ERRORS;

7.结束整个重定义
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('liushiming', 'bigtable', 'bigtablebackup');
END;
/
【总结】做过一个大小2.3GB，总行数360万行的表，整个过程大概花了56秒的时间，整个过程还是相当快的。建议具体的生产环境的执行需要经过严格测试后执行，测试的过程中大概就能知道整个过程的执行时间长度。

使用在线重定义的一些限制条件：
（1） There must be enough space to hold two copies of the table.
（2） Primary key columns cannot be modified.
（3） Tables must have primary keys.
（4） Redefinition must be done within the same schema.
（5） New columns added cannot be made NOT NULL until after the redefinition operation.
（6） Tables cannot contain LONGs, BFILEs or User Defined Types.
（7） Clustered tables cannot be redefined.
（8） Tables in the SYS or SYSTEM schema cannot be redefined.
（9） Tables with materialized view logs or materialized views defined on them cannot be redefined.
（10） Horizontal sub setting of data cannot be performed during the redefinition.
 
在Oracle 10.2.0.4和11.1.0.7 版本下，在线重定义可能会遇到如下bug：
       Bug 7007594 - ORA-600 [12261]
       http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218681.aspx

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31448824/viewspace-2136613/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31448824/viewspace-2136613/