oracle安装分区表,Oracle普通表修改为分区表的方法

数据库版本是9.2.0.6

需要将几个1T左右的普通表变更为分区表.

尝试过下面三种方法:

1.先创建空的分区表,然后将原表中的数据insert进去;

2.使用Oracle的在线重定义工具

3.CTAS方法.

执行办法,比较:

第一种方法：先创建空的分区表,然后将原表中的数据insert进去

SQL> create table t as select object_id,object_name from all_objects;

Table created.

create table t_new

(OBJECT_ID NUMBER,

OBJECT_NAME VARCHAR2(30))

partition by range(object_id)

(partition P1 values less than (100),

partition P2 values less than (1000),

partition P3 values less than (10000),

partition P4 values less than (MAXVALUE));

然后insert into t_new select * from t;

rename t to t_old;

rename t_new to t;

然后将原来t上的索引在新的t表上新建,完成.

这个方法比较麻烦，也是最简单易学的一种操作方法,对于小表,而且是静止状态下的表,可以进行.

第二种方法：使用Oracle的在线重定义工具

Oracle提供的在线重定义的工具无疑对于在线运行的表的重建有很重要的意义.他可以在表在线的情况下重定义表的结构.

如果需要重定义的表上面有主键，则可以直接使用主键进行重定义，如果表上没有主键,则可以使用rowid的方式重新定义.

我们来演示一下使用rowid重定义表的方法

首先验证是否可以进行在线重定义

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('CHARSI','T',dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed.

验证我们的测试表和数据

SQL> select count(1) from t;

COUNT(1)

----------

49226

SQL> select count(1) from t_new;

COUNT(1)

----------

0

SQL>

SQL> select owner,segment_name,partition_name from dba_segments where owner='CHARSI' and segment_name like 'T%';

OWNER SEGMENT_NAME PARTITION_NAME

------------------------------ -------------------------------------------------- ------------------------------

CHARSI T_NEW P3

CHARSI T_NEW P2

CHARSI T

CHARSI T_NEW P1

CHARSI T_NEW P4

开始在线重定义：

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE('CHARSI','T','T_NEW',OPTIONS_FLAG=>dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed.

SQL> select count(1) from t;

COUNT(1)

----------

49226

SQL> select count(1) from t_new;

COUNT(1)

----------

49226

验证同步的情况：(可以看到，因为我们没有执行同步，所以T表有更新的话，新表的数据并没有实时更新)

SQL> insert into t select * from t where rownum <11;

10 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from t;

COUNT(1)

----------

49236

SQL> select count(1) from t_new;

COUNT(1)

----------

49226

我们手工做一次数据同步：(可以看到手工做同步之后数据又同步过去了)

SQL> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('CHARSI','T','T_NEW');

PL/SQL procedure successfully completed.

SQL> select count(1) from t;

COUNT(1)

----------

49236

SQL> select count(1) from t_new;

COUNT(1)

----------

49236

结束数据同步：

SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('CHARSI','T','T_NEW');

PL/SQL procedure successfully completed.

在线重定义的方法原理是首先将原表的数据和新表的数据做一次同步,之后每执行一次SYNC_INTERIM_TABLE就会将原表更新的数据同步到新表中,知道执行结束同步命令FINISH_REDEF_TABLE.

但是有一个问题是,如果我需要做的表有1T，那么由于快照过期的问题或者undo表空间不够的原因,根本不能完成第一次同步.

会报下面的错误：

ERROR at line 1:

ORA-12008: error in materialized view refresh path

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'

ORA-06512: at "SYS.DBMS_REDEFINITION", line 8

ORA-06512: at "SYS.DBMS_REDEFINITION", line 146

ORA-06512: at line 1

第三种方法：使用create table as select 的方法

这种方法在使用得当的时候很好用,因为相比先创建表再insert，它基本很少会使用undo表空间和临时表空间,而且速度也比较快.

如果表比较大(我测试用的表有1T以上),而表中的数据不会实时更新,则推荐使用这种方法.

create table t_old

partition by range(object_id)

(partition P1 values less than (100),

partition P2 values less than (1000),

partition P3 values less than (10000),

partition P4 values less than (MAXVALUE))

as select * from t;[@more@]