如何将一个普通的非分区表进行分区4 DBMS_REDEFINITION

如何将一个普通的非分区表进行分区4 DBMS_REDEFINITION ## 参考文档： Case Study for a Large Non+Partition Table to a Partition Table with Online Transactions Occuring How To Partition Existing Table Using DBMS_REDEFINITION (Doc ID 472449.1) How To Change the Partition Column Of A Partitioned Table Using DBMS_Redefinition (Doc ID 846405.1) ## 简介： 本次演示DBMS_REDEFINITION在线重定义分区表的几个步骤： 1：建立一个需要被重定义的表A(非分区) 2：建立一个中间表B(分区表) 3：用表B结构来重定义表A，使A表分区 执行在线重定义所需要权限：     角色：EXECUTE_CATALOG_ROLE     权限：CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE、SELECT ANY TABLE ## 1、创建模拟环境 创建用户 create user PARTION identified by partion; grant dba to partion; connect partion/partion 创建一个表 CREATE TABLE unpar_table ( a NUMBER, y number, name VARCHAR2(100), date_used date); 为表添加主键 alter table unpar_table ADD (CONSTRAINT unpar_table_pk PRIMARY KEY (a,y)); 在表中插入数据 ++ load table with 1,000,000 rows begin for i in 1 .. 1000 loop for j in 1 .. 1000 loop insert into unpar_table values ( i, j, dbms_random.random, sysdate+j ); end loop; end loop; end; / commit; +++++++++++++++++++++++++++++++++++++++++++++++++ 收集统计信息，并查看表的行数 EXEC DBMS_STATS.gather_table_stats('partion', 'unpar_table', cascade => TRUE); SELECT num_rows FROM user_tables WHERE table_name = 'UNPAR_TABLE'; ## 2、创建分区临时表 CREATE TABLE par_table ( a NUMBER, y number, name VARCHAR2(100),date_used DATE) PARTITION BY RANGE (date_used) (PARTITION unpar_table_12 VALUES LESS THAN (TO_DATE('10/05/2015', 'DD/MM/YYYY')), PARTITION unpar_table_15 VALUES LESS THAN (TO_DATE('15/05/2015', 'DD/MM/YYYY')), PARTITION unpar_table_MX VALUES LESS THAN (MAXVALUE)); ++++++++++++++++++++++++++++++++++++++++++++++++++ ## 3、dbms_redefinition在线重定义 检查是否可以重定义表 EXEC Dbms_Redefinition.can_redef_table('partion', 'unpar_table'); 执行在线同步 BEGIN DBMS_REDEFINITION.start_redef_table( uname => 'partion', orig_table => 'unpar_table', int_table => 'par_table'); END; / 查询相关视图 select mview_name,container_name, build_mode from user_mviews; MVIEW_NAME CONTAINER_NAME BUILD_MOD ++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++ +++++++++ PAR_TABLE PAR_TABLE PREBUILT 在线同步过程中，往表中插入数据 SQL> begin for i in 2000 .. 2010 loop for j in 2000 .. 2100 loop insert into unpar_table values ( i, j, dbms_random.random, sysdate+j ); end loop; end loop; end; / commit; 查询数据是否插入 SQL> select count(*) from MLOG$_UNPAR_TABLE;   COUNT(*) ++++++++++       1000 如果在执行完成DBMS_REDEFINITION.START_REDEF_TABLE过程后，立即执行DBMS_REDEFINITION.FINISH_REDEF_TABLE 过程， 这会导致在重定义表上执行了大量的DML操作，那么可以选择执行一次或多次的DBMS_REDEFINITION.SYNC_INTERIM_TABLE过程， 以减少最后一步执行FINISH_REDEF_TABLE过程时的锁定时间    SQL> BEGIN dbms_redefinition.sync_interim_table( uname => 'partion', orig_table => 'unpar_table', int_table => 'par_table'); END; ALTER TABLE par_table ADD (CONSTRAINT par_table_pk2 PRIMARY KEY (a,y));  EXEC DBMS_STATS.gather_table_stats('partion', 'par_table', cascade => TRUE); ##  4、完成表的重定义。  这个过程中，原始表会被独占模式锁定一小段时间，具体时间和表的数据量有关 BEGIN dbms_redefinition.finish_redef_table( uname => 'partion', orig_table => 'unpar_table', int_table => 'par_table'); END; / select count(*) from par_table ;   COUNT(*) ++++++++++    1001000         select count(*) from unpar_table ;   COUNT(*) ++++++++++    1001000 ##  5、查询原表是否成为分区方式 SELECT partitioned FROM user_tables WHERE table_name = 'UNPAR_TABLE'; PAR +++ YES SQL> SELECT partitioned FROM user_tables WHERE table_name = 'PAR_TABLE'; PAR --- NO SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'UNPAR_TABLE'; PARTITION_NAME NUM_ROWS ++++++++++++++++++++++++++++++ ++++++++++ UNPAR_TABLE_12 169000 UNPAR_TABLE_15 5000 UNPAR_TABLE_MX 827000 drop TABLE par_table cascade constraints; ## 6.约束之类的药过去可以通过下面的步骤，索引建议自己再新建索引 DECLARE num_errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('USERPC', 'TRX_BASE', 'TRX_BASE_TMP',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors); END; / ## 7.正常来说是无法通过在线重定义修改表的类型的，但是 number 转varchar 可以通过下面的办法  参考文档： ORA-42016: Shape Of Interim Table Does Not Match Specified Column Mapping (Doc ID 2016240.1) https://www.itpub.net/forum.php?mod=viewthread&page=2&tid=1280805 SQL>  create table  UAS_USER ( USER_ID     number,   USER_NO      VARCHAR2(256),   USERNAME     VARCHAR2(256),   PASSWORD     VARCHAR2(256),   MODIFY_TIME  DATE not null,   MODIFY_ADMIN VARCHAR2(64) );  2    3    4    5    6    7    8    9   Table created. SQL>  alter table UAS_USER   add constraint PK_UAS_USER primary key (USER_ID);   2   Table altered. SQL> insert into UAS_USER values ( 1,'1','zc','zc',sysdate,'zc'); 1 row created. SQL> insert into UAS_USER values ( 2,'1','zc','zc',sysdate,'zc'); 1 row created. SQL> commit; Commit complete. SQL> commit; Commit complete. SQL> create table INTER_UAS_USER ( USER_ID      varchar2(20) not null,   USER_NO      VARCHAR2(256),   USERNAME     VARCHAR2(256),   PASSWORD     VARCHAR2(256),   MODIFY_TIME  DATE not null,   MODIFY_ADMIN VARCHAR2(64) );   2    3    4    5    6    7    8    9   Table created. SQL> alter table INTER_UAS_USER   add constraint PK_INTER_UAS_USER primary key (USER_ID);  2   Table altered.   SQL> exec dbms_redefinition.can_redef_table('ZC','UAS_USER'); PL/SQL procedure successfully completed. SQL> exec dbms_redefinition.start_redef_table('ZC','UAS_USER','INTER_UAS_USER', 'to_char(USER_ID) USER_ID, USER_NO USER_NO, USERNAME USERNAME, PASSWORD PASSWORD, MODIFY_TIME MODIFY_TIME, MODIFY_ADMIN MODIFY_ADMIN', dbms_redefinition.cons_use_rowid); PL/SQL procedure successfully completed. SQL> exec dbms_redefinition.sync_interim_table('ZC','UAS_USER','INTER_UAS_USER'); PL/SQL procedure successfully completed. SQL>  SQL> exec dbms_redefinition.finish_redef_table('ZC','UAS_USER','INTER_UAS_USER'); PL/SQL procedure successfully completed. SQL> desc uas_user;  Name    Null?    Type  ----------------------------------------- -------- ----------------------------  USER_ID    NOT NULL VARCHAR2(20)  USER_NO     VARCHAR2(256)  USERNAME     VARCHAR2(256)  PASSWORD     VARCHAR2(256)  MODIFY_TIME    NOT NULL DATE  MODIFY_ADMIN     VARCHAR2(64)