DBMS_REDEFINITION

在线将普通表转成分区表 用exp命令将生产数据库里面的CRM.READ_INFO表导出来。因为这张表现在已经有1千多万条数据、1.2个G了，ORACLE建议将2个G以上的表定义成分区表，而且根据公司业务的特殊需要，所以计划将这张表在线转成分区表（有JOB来对这张表插入数据）。[@more@]

DBMS_REDEFINITION包的使用：在线将普通表转成分区表

1、用exp命令将生产数据库里面的CRM.READ_INFO表导出来。因为这张表现在已经有1千多万条数据、1.2个G了，ORACLE建议将2个G以上的表定义成分区表，而且根据公司业务的特殊需要，所以计划将这张表在线转成分区表（有JOB来对这张表插入数据）。

exp system/system tables= READ_INFO file=/u01/app/oracle/DBA/store_source/back_read_info-20071029.dmp log=back_read_info-20071029.log rows=y indexes=n direct=y buffer=655360

2、在测试数据库TEST里面添加相关的用户、表空间、数据文件
SQL> CONN /AS SYSDBA
SQL> CREATE TABLESPACE CRM DATAFILE '/opt/oracle/oradata/TEST/crm.dbf' SIZE 4000M;
SQL> CREATE USER CRM IDENTIFIED BY CRM;
SQL> ALTER SYSTEM IDENTIFIED BY SYSTEM;

3、将从生产库里面导出的dmp文件拷贝到测试数据库所在的服务器上面：/opt/oracle/back_read_info-20071029.dmp

4、使用imp命令将数据导入测试数据库TEST
[oracle@linux1 ~]$ imp system/system file=/opt/oracle/back_read_info-20071029.dmp fromuser=crm touser=crm feedback=100000 log=/opt/oracle/imp.log

Import: Release 10.1.0.3.0 - Production on Thu Nov 1 13:58:16 2007

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via direct path
import done in ZHS16GBK character set and UTF8 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses US7ASCII character set (possible charset conversion)
. importing CRM's objects into CRM
. . importing table "READ_INFO"
...........................................................................
....................................
11145618 rows imported
Import terminated successfully without warnings.

5、在测试库上面进行在线重定义的表自行验证，看该表是否可以重定义
SQL> CONN /AS SYSDBA
SQL> ALTER SYSTEM IDENTIFIED BY SYSTEM;
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('CRM', 'READ_INFO',DBMS_REDEFINITION.CONS_USE_ROWID);

6、新建一个和源表表结构一样的分区表，作为中间表。按日期范围分区
SQL> CREATE TABLE CRM.TEMP_READ_INFO
(
EID INTEGER,
GUID VARCHAR2(100 BYTE),
IP VARCHAR2(100 BYTE),
READ_DATE DATE,
BOOKNAME VARCHAR2(100 BYTE),
VERSION INTEGER,
ISVIP INTEGER,
VIPNAME VARCHAR2(100 BYTE),
SYSTIME DATE,
BOOKTYPE VARCHAR2(100 BYTE),
FIRST_READ INTEGER DEFAULT 0
)
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
PARTITION BY RANGE (SYSTIME)
SUBPARTITION BY HASH (EID) SUBPARTITIONS 4
(PARTITION P1 VALUES LESS THAN (TO_DATE('2007-10-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) (SUBPARTITION P1_SUBP1 TABLESPACE CRM,
SUBPARTITION P1_SUBP2 TABLESPACE CRM,
SUBPARTITION P1_SUBP3 TABLESPACE CRM,
SUBPARTITION P1_SUBP4 TABLESPACE CRM),
PARTITION P2 VALUES LESS THAN (TO_DATE('2007-11-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
(SUBPARTITION P2_SUBP1 TABLESPACE CRM,
SUBPARTITION P2_SUBP2 TABLESPACE CRM,
SUBPARTITION P2_SUBP3 TABLESPACE CRM,
SUBPARTITION P2_SUBP4 TABLESPACE CRM),
PARTITION P3 VALUES LESS THAN (TO_DATE('2007-12-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
(SUBPARTITION P3_SUBP1 TABLESPACE CRM,
SUBPARTITION P3_SUBP2 TABLESPACE CRM,
SUBPARTITION P3_SUBP3 TABLESPACE CRM,
SUBPARTITION P3_SUBP4 TABLESPACE CRM),
PARTITION P4 VALUES LESS THAN (TO_DATE('2008-1-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
(SUBPARTITION P4_SUBP1 TABLESPACE CRM,
SUBPARTITION P4_SUBP2 TABLESPACE CRM,
SUBPARTITION P4_SUBP3 TABLESPACE CRM,
SUBPARTITION P4_SUBP4 TABLESPACE CRM),
PARTITION P5 VALUES LESS THAN (TO_DATE('2008-2-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
(SUBPARTITION P5_SUBP1 TABLESPACE CRM,
SUBPARTITION P5_SUBP2 TABLESPACE CRM,
SUBPARTITION P5_SUBP3 TABLESPACE CRM,
SUBPARTITION P5_SUBP4 TABLESPACE CRM),
PARTITION P6 VALUES LESS THAN (TO_DATE('2008-3-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
(SUBPARTITION P6_SUBP1 TABLESPACE CRM,
SUBPARTITION P6_SUBP2 TABLESPACE CRM,
SUBPARTITION P6_SUBP3 TABLESPACE CRM,
SUBPARTITION P6_SUBP4 TABLESPACE CRM),
PARTITION P7 VALUES LESS THAN (TO_DATE('2008-4-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
(SUBPARTITION P7_SUBP1 TABLESPACE CRM,
SUBPARTITION P7_SUBP2 TABLESPACE CRM,
SUBPARTITION P7_SUBP3 TABLESPACE CRM,
SUBPARTITION P7_SUBP4 TABLESPACE CRM),
PARTITION P8 VALUES LESS THAN (TO_DATE('2008-5-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
(SUBPARTITION P8_SUBP1 TABLESPACE CRM,
SUBPARTITION P8_SUBP2 TABLESPACE CRM,
SUBPARTITION P8_SUBP3 TABLESPACE CRM,
SUBPARTITION P8_SUBP4 TABLESPACE CRM),
PARTITION P9 VALUES LESS THAN (TO_DATE('2008-6-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
(SUBPARTITION P9_SUBP1 TABLESPACE CRM,
SUBPARTITION P9_SUBP2 TABLESPACE CRM,
SUBPARTITION P9_SUBP3 TABLESPACE CRM,
SUBPARTITION P9_SUBP4 TABLESPACE CRM),
PARTITION P10 VALUES LESS THAN (TO_DATE('2008-7-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
(SUBPARTITION P10_SUBP1 TABLESPACE CRM,
SUBPARTITION P10_SUBP2 TABLESPACE CRM,
SUBPARTITION P10_SUBP3 TABLESPACE CRM,
SUBPARTITION P10_SUBP4 TABLESPACE CRM),
PARTITION P11 VALUES LESS THAN (TO_DATE('2008-8-1 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
(SUBPARTITION P11_SUBP1 TABLESPACE CRM,
SUBPARTITION P11_SUBP2 TABLESPACE CRM,
SUBPARTITION P11_SUBP3 TABLESPACE CRM,
SUBPARTITION P11_SUBP4 TABLESPACE CRM),
PARTITION P12 VALUES LESS THAN (MAXVALUE)
(SUBPARTITION P12_SUBP1 TABLESPACE CRM,
SUBPARTITION P12_SUBP2 TABLESPACE CRM,
SUBPARTITION P12_SUBP3 TABLESPACE CRM,
SUBPARTITION P12_SUBP4 TABLESPACE CRM));

7、删除READ_INFO、TEMP_READ_INFO表的物化视图以及日志
SQL> DROP MATERIALIZED VIEW LOG ON CRM.READ_INFO;
SQL> DROP MATERIALIZED VIEW LOG ON CRM.TEMP_READ_INFO;
SQL> DROP MATERIALIZED VIEW CRM.READ_INFO;
SQL> DROP MATERIALIZED VIEW CRM.READ_INFO;

8、执行表的在线重定义：
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('CRM', 'READ_INFO', 'TEMP_READ_INFO',NULL,DBMS_REDEFINITION.CONS_USE_ROWID);

9、执行把中间表的内容和数据源表进行同步。
SQL>EXECUTE DBMS_REDEFINITION.SYNC_INTERIM_TABLE('CRM','READ_INFO','TEMP_READ_INFO');

10、执行结束在线定义过程
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('CRM', 'READ_INFO', 'TEMP_READ_INFO');

11、查看数据字典，可以看到改表已经成为了分区表。
SQL> SELECT COUNT(*) FROM CRM.READ_INFO;
COUNT(*)
----------
11145618
SQL> SELECT COUNT(*) FROM CRM.READ_INFO PARTITION (P1);
COUNT(*)
----------
6679953
SQL> SELECT COUNT(*) FROM CRM.READ_INFO PARTITION (P2);

COUNT(*)
----------
4465481

SQL> SELECT COUNT(*) FROM CRM.READ_INFO PARTITION (P12);

COUNT(*)
----------
184
至此普通表转为分区操作完成

12、如果执行在线重定义的过程中出错
可以在执行DBMS_REDEFINITION.START_REDEF_TABLE之后到执行DBMS_REDEFINITION.FINISH_REDEF_TABLE之前的时间里执行：DBMS_REDEFINITION.ABORT_REDEF_TABLE(USER, 'READ_INFO', 'TEMP_READ_INFO')以放弃执行在线重定义。

13、如果出现以下错误：
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('CRM', 'READ_INFO', 'TEMP_READ_INFO',NULL,DBMS_REDEFINITION.CONS_USE_ROWID);
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('CRM', 'READ_INFO', 'TEMP_READ_INFO',NULL,DBMS_REDEFINITION.CONS_USE_ROWID); END;
*
ERROR AT LINE 1:
ORA-12091: CANNOT ONLINE REDEFINE TABLE "CRM"."READ_INFO" WITH
MATERIALIZED VIEWS
ORA-06512: AT "SYS.DBMS_REDEFINITION", LINE 137
ORA-06512: AT "SYS.DBMS_REDEFINITION", LINE 1478
ORA-06512: AT LINE 1
用这句删除MATERIALIZED VIEW 即可继续进行
DROP MATERIALIZED VIEW LOG ON CRM.READ_INFO;
OR DROP MATERIALIZED READ_INFO;

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

转载于:http://blog.itpub.net/9821375/viewspace-996407/