Oracle普通表转化为分区表

普通表转化为分区表：Oracle在线重定义表功能
================================================================================
Oracle的普通表不能通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变

方法一：利用原表重建分区表

创建原表
SQL> create table t as select rownum id,obj.* from dba_objects obj;

创建分区表
------------------------------------------------------------------
create table t_new  partition by range (id)
(partition p1 values less than (20000),
 partition p2 values less than (40000),
 partition p3 values less than (60000),
 partition p4 values less than (maxvalue)
) as select * from t;
------------------------------------------------------------------

重命名表
SQL> rename t to t_old;
SQL> rename t_new to t;

SQL> select * from t partition(p1)

优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后

数据已经在分布到各个分区中了。

不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执

行create table语句和rename t_new to t语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句

后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。

适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。

方法二：使用交换分区的方法

创建原表
SQL> create table t as select rownum id,obj.* from dba_objects obj;
SQL> select count(*) from t;

创建分区表(分区p1要能够装载t表中的所有记录)
------------------------------------------------------------------
create table t_new  partition by range (id)
(partition p1 values less than (60000),
 partition pm values less than (maxvalue)
) as select * from t where 1=2;
------------------------------------------------------------------

SQL> alter table t_new exchange partition p1 with table t;
SQL> select count(*) from t;
SQL> select count(*) from t_new;

SQL> rename t to t_old;
SQL> rename t_new to t;

优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区

中的分布没有进一步要求的话，实现比较简单。在执行完rename操作后，可以检查t_old中是否存在数据，如果

存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。

不足：仍然存在一致性问题，交换分区之后rename t_new to之前，查询、更新和删除会出现错误或访问不到数

据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。

适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。

方法三：利用在线重定义功能(Oracle9i以上版本)
======================================================================================
exec dbms_redefinition.can_redef_table('user', 'table',dbms_redefinition.cons_use_pk);

create table user.table_new;

exec dbms_redefinition.start_redef_table('user','table','table_new');
exec dbms_redefinition.sync_interim_table('user','table','table_new');
exec dbms_redefinition.finish_redef_table('user','table','table_new');
======================================================================================

创建原表
SQL> create table t as select rownum id,obj.* from dba_objects obj;
SQL> alter table t add constraint t_ind primary key (id) using index tablespace users;
SQL> select count(*) from scott.t;
SQL> select table_name, tablespace_name from user_tables;

1、检查t表是否可进行在线重定义
------------------------------------------------------------------------------
begin
 dbms_redefinition.can_redef_table('SCOTT','T',dbms_redefinition.cons_use_pk);
end;
------------------------------------------------------------------------------
如果有错误提示, 则表示该表不可以进行在线重定义操作

2、创建中间表, 中间表具有和在线重定义的目标表具有相同的属性
------------------------------------------------------------------------------
create table scott.t_new(
  id number not null,
  owner varchar2(30 byte) not null,
  object_name varchar2(30 byte) not null,
  subobject_name varchar2(30 byte),
  object_id number not null,
  data_object_id number,
  object_type varchar2(18 byte),
  created date not null,
  last_ddl_time date not null,
  timestamp varchar2(19 byte),
  status varchar2(7 byte),
  temporary varchar2(1 byte),
  generated varchar2(1 byte),
  secondary varchar2(1 byte),
  constraint t_ind_new primary key(id)
  using index tablespace users nologging )
  partition by range(id)
   (partition p1 values less than (20001) tablespace users,
    partition p2 values less than (40001) tablespace users,
    partition p3 values less than (60001) tablespace users,
    partition p4 values less than (80001) tablespace users,
    partition p5 values less than (maxvalue) tablespace users
   );
------------------------------------------------------------------------------

3、开始重组过程
SQL> exec dbms_redefinition.start_redef_table('SCOTT', 'T','T_NEW');
 
4、重组过程中, 可以多次同步中间表.
SQL> exec dbms_redefinition.sync_interim_table('SCOTT', 'T','T_NEW');

5、完成在线重定义.
SQL> exec dbms_redefinition.finish_redef_table('SCOTT', 'T','T_NEW');

6、删除中间表.
SQL> drop table scott.t_new;

7、检查战果.
SQL> col segment_name for a20
SQL> select segment_name,partition_name,segment_type,tablespace_name,bytes from user_segments;

8、附注：在oracle 10g中, dbms_redefinition包增加了copy_table_dependents,

register_dependent_object, unregister_dependent_object三个存储过程, 分别用来复制, 注册, 注销表的

依赖对象, 如索引, 约束, 触发器等，并增加了dba_redefinition_errors数据字典视图, 用来查看在线重定义

过程中出现的错误

优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的

可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建

立各种约束，可以做到切换完成后不再需要任何额外的管理操作。

不足：实现上比上面两种略显复杂。

 

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

转载于:http://blog.itpub.net/17012874/viewspace-693289/