如何将一个普通的非分区表进行分区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)