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