將普通表格轉化分區表的方法大致有四種： A. 通過 Export/import 方法B. 通過 Insert with a subquery 方法C. 通過 Partition Exchange 方法D. 通過 DBMS_REDEFINITION 方法 下麵舉例使用DBMS_REDEFINITION ...
將普通表格轉化分區表的方法大致有四種：
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;
