ORACLE将普通表转换成分区表

将普通表转换成分区表的两种方式

一、rename方式

实战：

--1.创建测试表
CREATE TABLE test_fan AS SELECT * FROM Dba_Objects;
--2.创建测试表对应的分区表
SELECT dbms_metadata.get_ddl('TABLE','TEST_FAN') FROM dual;
create table man_dev.test_fan_bak 
 (owner , 
  object_name , 
  subobject_name , 
  object_id , 
  data_object_id , 
  object_type , 
  created , 
  last_ddl_time , 
  timestamp , 
  status , 
  temporary , 
  generated , 
  secondary , 
  namespace , 
  edition_name 
  )partition by hash(object_id)
  (partition p1,partition p2,partition p3) 
  as select * from test_fan;
  --3.使用rename语法改变表名
  RENAME test_fan TO test_fan_old;
  RENAME test_fan_bak TO test_fan;
  --4.验证
  SELECT COUNT(1) FROM test_fan PARTITION(p1);

优点:简单，采用ddl语句，不产生undo，效率不错

缺点：无法锁表，一致性风险高。1.create bak时 2.rename时

二、在线重定义（dbms_redefinition包）

实战：

无主键方式：

--无主键
--1.创建表数据和对应表分区结构
DROP TABLE test_fan;
DROP TABLE test_fan_bak;
CREATE TABLE test_fan AS SELECT object_name,object_id FROM Dba_Objects;
CREATE TABLE test_fan_bak
(object_name VARCHAR2(30),
object_id    NUMBER)
PARTITION BY HASH(object_id)
(PARTITION p1,PARTITION p2 ,PARTITION p3);
--2.使用dbms_redefinition系统包
BEGIN
  --检查表是否可以重定义
  dbms_redefinition.can_redef_table(uname        => 'MAN_DEV',
                                    tname        => 'TEST_FAN',
                                    options_flag => 2 --rowid
                                    );
  --开始在线重定义                                  
  dbms_redefinition.start_redef_table(uname        => 'MAN_DEV',
                                      orig_table   => 'TEST_FAN',
                                      int_table    => 'TEST_FAN_BAK',
                                      col_mapping  => 'object_name object_name,object_id object_id',
                                      options_flag => 2 );
  --完成
  dbms_redefinition.finish_redef_table(uname      => 'MAN_DEV',
                                       orig_table => 'TEST_FAN',
                                       int_table  => 'TEST_FAN_BAK');                                                                      
  END;
--3.验证  
  SELECT COUNT(1) FROM test_fan PARTITION(p1);

主键方式：

--主键
--1.创建表数据和对应表分区结构
DROP TABLE test_fan;
DROP TABLE test_fan_bak;
CREATE TABLE test_fan AS SELECT object_name,object_id FROM Dba_Objects WHERE OBJECT_ID IS NOT NULL;
CREATE TABLE test_fan_bak
(object_name VARCHAR2(30),
object_id    NUMBER)
PARTITION BY HASH(object_id)
(PARTITION p1,PARTITION p2 ,PARTITION p3);
--2.添加主键
ALTER TABLE test_fan ADD CONSTRAINT PK_test_fan PRIMARY KEY(object_id);
ALTER TABLE test_fan_bak ADD CONSTRAINT PK_test_fan_bak PRIMARY KEY(object_id);
--3.使用dbms_redefinition系统包
BEGIN
  --检查表是否可以重定义
  dbms_redefinition.can_redef_table(uname        => 'MAN_DEV',
                                    tname        => 'TEST_FAN',
                                    options_flag => 1 --主键object_id
                                    );
  --开始在线重定义                                  
  dbms_redefinition.start_redef_table(uname        => 'MAN_DEV',
                                      orig_table   => 'TEST_FAN',
                                      int_table    => 'TEST_FAN_BAK',
                                      options_flag => 1 );
  --完成
  dbms_redefinition.finish_redef_table(uname      => 'MAN_DEV',
                                       orig_table => 'TEST_FAN',
                                       int_table  => 'TEST_FAN_BAK');                                                                      
  END;
--4.验证  
  SELECT COUNT(1) FROM test_fan PARTITION(p1);

优点：一致性风险低，只有在切换一瞬间可能产生

缺点：稍微复杂

总结：更改表结构需要考虑数据一致性风险，尽量在停机状态下或者业务量极少情况下实施。