oracle非分区表转为分区表

方法一：

 

create table TBL_MERCHANT_ACCOUNT_DETAIL2 partition by (WORKDATE) 
(
partition P1 values('20121023'),
partition P2 values('20121024'),
partition P3 values('20121025'),
partition P4 values(default)
)
as select * from PDS.TBL_MERCHANT_ACCOUNT_DETAIL;

rename TBL_MERCHANT_ACCOUNT_DETAIL to TBL_MER_ACCOUNT_DETAIL_OLD;
rename TBL_MERCHANT_ACCOUNT_DETAIL2 to TBL_MERCHANT_ACCOUNT_DETAIL;

 

优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。   
　　不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。   
　　适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。

 

 

分区表的使用中，where条件如果有个分区字段，会自动检索该分区字段所在分区的记录，

因此，以下两句的执行效率是一致的：

 

select * from PDS.TBL_MERCHANT_ACCOUNT_DETAIL d where d.WORKDATE='20121024' and d.STEP_NAME='balanceMerchantAccount';

select * from PDS.TBL_MERCHANT_ACCOUNT_DETAIL  partition(P2) d where d.STEP_NAME='balanceMerchantAccount';

 

 

方法二：

在线重定义

 

 写道

1 创建测试表
用sys用户创建测试表

SQL> CREATE TABLE HR.ST (ID NUMBER, TIME DATE);

Table created.

SQL> INSERT INTO HR.ST SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

50416 rows created.

SQL> commit;

Commit complete.

2 授权

SQL> grant execute on DBMS_REDEFINITION to hr;

Grant succeeded.

SQL> grant CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE to hr;

Grant succeeded.

3 新建中间表(分区的表结构)

SQL> create table mid_st(id number primary key, time date) partition by range(time)

2 (

3 partition p1 values less than(to_date('2004-7-1', 'yyyy-mm-dd')),

4 partition p2 values less than(to_date('2005-1-1', 'yyyy-mm-dd')),

5 partition p3 values less than(to_date('2005-7-1', 'yyyy-mm-dd')),

6 partition p4 values less than(maxvalue)

7 );

Table created

4 验证

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'ST', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed

5 在线重定义

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST');

PL/SQL procedure successfully completed

6 同步数据(可选)

如果在执行DBMS_REDEFINITION.START_REDEF_TABLE()过程和执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程直接在重定义表上执行了大量的DML操作，那么可以选择执行一次或多次的SYNC_INTERIM_TABLE()过程，以减少最后一步执行FINISH_REDEF_TABLE()过程时的锁定时间。

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, 'ST', 'MID_ST');

PL/SQL procedure successfully completed

7 结束重定义

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'ST', 'MID_ST');

PL/SQL procedure successfully completed

8 查看分区结果

SQL> select a.table_name, a.partition_name from user_tab_partitions a;

TABLE_NAME PARTITION_NAME

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

ST P1

ST P2

ST P3

ST P4

9 放弃在线重定义

可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行：

DBMS_REDEFINITION.abort_redef_table(user, 'st', 'mid_st')以放弃执行在线重定义。

10 常见问题

10.1 无主键

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'ST', DBMS_REDEFINITION.CONS_USE_PK);

BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'ST', DBMS_REDEFINITION.CONS_USE_PK); END;

*

ERROR at line 1:

ORA-12089: cannot online redefine table "HR"."ST" with no primary key

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

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

ORA-06512: at line 1



出错了, 该表上缺少主键, 为该表建主键. 再执行验证.

SQL> alter table st add constraint pk_t primary key(id);

Table altered.



用这句删除materialized view 即可继续进行

drop materialized view log on <tablename>;

drop materialized view log on t; OR drop materialized t;

10.2 未授权

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST');

begin DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST'); end;

ORA-01031: insufficient privileges

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

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

ORA-06512: at line 2

 

优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。   
不足：实现上比上面两种略显复杂。   
适用于各种情况。  
　　然而，在线表格重定义也不是完美无缺的。下面列出了Oracle9i重定义过程的部分限制。
　　你必须有足以维护两份表格拷贝的空间。 
 　　你不能更改主键栏。 
 　　表格必须有主键。 
 　　必须在同一个大纲中进行表格重定义。 
 　　在重定义操作完成之前，你不能对新加栏加以NOT NULL约束。 
 　　表格不能包含LONG、BFILE以及用户类型（UDT）。 
 　　不能重定义链表（clustered tables）。 
 　　不能在SYS和SYSTEM大纲中重定义表格。 
 　　不能用具体化视图日志（materialized view logs）来重定义表格；不能重定义含有具体化视图的表格。 
 　　不能在重定义过程中进行横向分集（horizontal subsetting）。

 

 

方法三：

交换分区

步骤：   
　　SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);   
　　表已创建。   
　　SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;   
　　已创建6264行。   
　　SQL> COMMIT;   
　　提交完成。   
　　SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION 
BY RANGE (TIME)   
　　2 (PARTITION P1 VALUES LESS THAN (TO_DATE(’2005-7-1’, ’YYYY-MM-DD’)),   
　　3 PARTITION P2 VALUES LESS THAN (MAXVALUE));   
　　表已创建。   
　　SQL> ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;   
　　表已更改。   
　　SQL> RENAME T TO T_OLD;   
　　表已重命名。   
　　SQL> RENAME T_NEW TO T;   
　　表已重命名。   
　　SQL> SELECT COUNT(*) FROM T;   
　　COUNT(*)  
　　----------  
　　6264   

 

 

 

 

优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。   
　　不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。   
　　适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。