将普通表格转化分区表的方法大致有四种：
A. 通过 Export/import 方法
B. 通过 Insert with a subquery 方法
C. 通过 Partition Exchange 方法
D. 通过 DBMS_REDEFINITION 方法
下面举例使用DBMS_REDEFINITION的方法将普通表格转化成分区表
1.创建测试表
SQL> CREATE TABLE T(
a NUMBER,
y number,
name VARCHAR2(100),
date_used date,
constraint pk_ay primary key(a,y));
Table created.
2.生成测试数据
begin
for i in 1 .. 1000
loop
for j in 1 .. 1000
loop
insert into t values ( i, j, dbms_random.random, sysdate-j );
end loop;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
3.收集T表格的统计信息
SQL> EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘T’, cascade => TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT num_rows FROM user_tables WHERE table_name = ‘T’;
NUM_ROWS
———-
1000000
4.创建分区表(中间临时表）
SQL> CREATE TABLE p_t(
a NUMBER,
y number,
name VARCHAR2(100),
date_used DATE)
PARTITION BY RANGE (date_used)
(PARTITION unpar_table_15 VALUES LESS THAN (TO_DATE(’01/01/2016′, ‘DD/MM/YYYY’)),
PARTITION unpar_table_16 VALUES LESS THAN (TO_DATE(’01/01/2017′, ‘DD/MM/YYYY’)),
PARTITION unpar_table_17 VALUES LESS THAN (TO_DATE(’01/01/2018′, ‘DD/MM/YYYY’)),
PARTITION unpar_table_MX VALUES LESS THAN (MAXVALUE));
Table created.
5.检验表格能否重定义
SQL> exec dbms_Redefinition.can_redef_table(‘SCOTT’, ‘T’);
PL/SQL procedure successfully completed.
6.开始在线重定义，此过程会创建物化视图P_T和物化视图日志MLOG$_T
SQL> BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => ‘SCOTT’,
orig_table => ‘T’,
int_table => ‘P_T’);
END;
/
PL/SQL procedure successfully completed.
SQL> select count(*) from t;
COUNT(*)
———-
1000000
Elapsed: 00:00:00.05
SQL> select count(*) from p_t;
COUNT(*)
———-
1000000
SQL> select mview_name,container_name, build_mode from user_mviews;
MVIEW_NAME CONTAINER_NAME BUILD_MOD
—————————— —————————— ———
P_T P_T PREBUILT
7. 向原表插入1000行数据，检查MLOG$_T表是否记录了更新
SQL> begin
for i in 1001 .. 1010
loop
for j in 1001 .. 1100
loop
insert into t values ( i, j, dbms_random.random, sysdate-j );
end loop;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select count(*) from MLOG$_T;
COUNT(*)
———-
1000
8.运行dbms_redefinition.sync_interim_table 填充表数据，在执行 dbms_redefinition.finish_redef_table前可以多次执行
SQL> BEGIN
dbms_redefinition.sync_interim_table(
uname => ‘SCOTT’,
orig_table => ‘T’,
int_table => ‘P_T’);
END;
/
PL/SQL procedure successfully completed.
SQL> select count(*) from p_t;
COUNT(*)
———-
1001000
SQL> ALTER TABLE p_t ADD (CONSTRAINT p_t_pk PRIMARY KEY (a,y));
Table altered.
SQL> EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘P_T’, cascade => TRUE);
PL/SQL procedure successfully completed.
9.使用dbms_redefinition.finish_redef_table 交换表名，过程中原表T会被锁定。
SQL> BEGIN
dbms_redefinition.finish_redef_table(
uname => ‘SCOTT’,
orig_table => ‘T’,
int_table => ‘P_T’);
END;
/
PL/SQL procedure successfully completed.
10.验证重定义的结果
SQL> SELECT partitioned FROM user_tables WHERE table_name = ‘T’;
PAR
—
YES
SQL> SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = ‘T’;
PARTITION_NAME NUM_ROWS
—————————— ———-
UNPAR_TABLE_15 178000
UNPAR_TABLE_16 366000
UNPAR_TABLE_17 365000
UNPAR_TABLE_MX 92000
11.删除中间临时表
drop TABLE p_t cascade constraints;