溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
在上一篇博文（Online Redefinition在線重定義（一））中，簡單地介紹了Oracle在線重定義特性進(jìn)行數(shù)據(jù)表在線結(jié)構(gòu)變動操作。本篇博文將演示一個較復(fù)雜的案例，在案例的復(fù)雜變化中進(jìn)行在線重定義及dbms_redefinition包各個關(guān)鍵方法的作用。
普通表改造分區(qū)表
--創(chuàng)建一張普通表t_wjq1
SEIANG@seiang11g>create table t_wjq1 as select object_id,object_name,created from dba_objects;
Table created.
SEIANG@seiang11g>desc t_wjq1
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OBJECT_ID NUMBER
OBJECT_NAME VARCHAR2(128)
CREATED DATE
SEIANG@seiang11g>select count(*) from t_wjq1;
COUNT(*)
----------
86997
--在表t_wjq1的object_id列上創(chuàng)建主鍵
SEIANG@seiang11g>alter table t_wjq1 add constraint pk_t_wjq1_id primary key(object_id);
Table altered.
SEIANG@seiang11g>
SEIANG@seiang11g>select index_name,index_type,table_owner,table_name from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME
------------------------------ --------------------------- ------------------------------ ------------------------------
PK_T_WJQ1_ID NORMAL SEIANG T_WJQ1
需求：重定義的內(nèi)容有以下幾個：
（1）使用object_id進(jìn)行分區(qū)
（2）created字段從date類型變?yōu)?/span>timestamp類型
（3）object_name字段改名為object_name_2。
--創(chuàng)建一張中間表t_wjq1_interim
SEIANG@seiang11g>create table t_wjq1_interim(
2 object_id number,
3 object_name_2 varchar2(128),
4 created timestamp
5 )
6 partition by range(object_id)
7 (
8 partition p1 values less than (5000),
9 partition p2 values less than (10000),
10 partition p3 values less than (50000),
11 partition p4 values less than (maxvalue)
12 );
Table created.
--查看中間表的分區(qū)情況
SEIANG@seiang11g>select table_name,partition_name from user_tab_partitions;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T_WJQ1_INTERIM P4
T_WJQ1_INTERIM P3
T_WJQ1_INTERIM P2
T_WJQ1_INTERIM P1
--首先，查看t_wjq1表是否支持重定義操作
SEIANG@seiang11g>exec dbms_redefinition.can_redef_table('SEIANG','T_WJQ1',options_flag=>dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
--開始重定義操作
SEIANG@seiang11g>exec dbms_redefinition.start_redef_table('SEIANG','T_WJQ1','T_WJQ1_INTERIM',col_mapping => 'object_id object_id, object_name object_name_2, to_timestamp(created) created',options_flag => dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.42
注意這個col_mapping映射關(guān)系設(shè)置，如果存在列名轉(zhuǎn)換，就在這里將列關(guān)系映射說明出來。如果需要進(jìn)行字段類型轉(zhuǎn)換，要書寫函數(shù)關(guān)系將映射計算規(guī)則定義出來。
Oracle在線重定義的基礎(chǔ)是物化視圖。此時，通過查看試圖user_mviews，可以看到有一個新的物化視圖生成，并且存在對應(yīng)的物化視圖日志。
SEIANG@seiang11g>select mview_name, container_name, query, REFRESH_METHOD from user_mviews;
MVIEW_NAME CONTAINER_NAME QUERY REFRESH_
------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------
T_WJQ1_INTERIM T_WJQ1_INTERIM select object_id object_id, object_name object_name_2, to_timestamp(created) cre FAST
Elapsed: 00:00:00.02
SEIANG@seiang11g>
SEIANG@seiang11g> select master,log_table from user_mview_logs;
MASTER LOG_TABLE
------------------------------ ------------------------------
T_WJQ1 MLOG$_T_WJQ1
Elapsed: 00:00:00.00
在線重定義的Start方法創(chuàng)建了一個Fast刷新模式的物化視圖對象t_wjq1_interim。物化視圖中最重要的物化視圖日志，名稱為MLOG$_T_WJQ1
--查看原始表和中間表的數(shù)據(jù)量
SEIANG@seiang11g>select count(*) from t_wjq1;
COUNT(*)
----------
86997
Elapsed: 00:00:00.01
SEIANG@seiang11g>
SEIANG@seiang11g>select count(*) from t_wjq1_interim;
COUNT(*)
----------
86997
Elapsed: 00:00:00.01
--沒有DML操作，所以物化視圖日志尚空
SEIANG@seiang11g>select * from mlog$_t_wjq1;
no rows selected
Elapsed: 00:00:00.00
SEIANG@seiang11g>select count(*) from mlog$_t_wjq1;
COUNT(*)
----------
0
Elapsed: 00:00:00.00
綜合上述內(nèi)容，說明start_redef_table的作用是下面幾個方面：
（1）以Interim數(shù)據(jù)表為名稱，創(chuàng)建一個Fast刷新模式的物化視圖對象；
（2）從源數(shù)據(jù)表中將數(shù)據(jù)加載到Interim中；
（3）創(chuàng)建物化視圖日志；
如果在這個過程中，發(fā)生了DML操作，也就是說在start過程或者之后有DML操作，有新數(shù)據(jù)插入或修改，如下操作所示：
--查看t_wjq1表的數(shù)據(jù)量和最大的object_id值
SEIANG@seiang11g>select count(*) from t_wjq1;
COUNT(*)
----------
86997
Elapsed: 00:00:00.00
SEIANG@seiang11g>select max(object_id) from t_wjq1;
MAX(OBJECT_ID)
--------------
89700
Elapsed: 00:00:00.01
--模擬DML操作：在表t_wjq1中插入數(shù)據(jù)庫
SEIANG@seiang11g>insert into t_wjq1 select object_id+90000,object_name,created from dba_objects;
87006 rows created.
Elapsed: 00:00:05.13
--再次查看原始表t_wjq1、中間表t_wjq1_interim以及物化視圖日志試圖的變化
SEIANG@seiang11g>select count(*) from t_wjq1;
COUNT(*)
----------
174003
Elapsed: 00:00:00.01
SEIANG@seiang11g>
SEIANG@seiang11g>select count(*) from t_wjq1_interim;
COUNT(*)
----------
86997
Elapsed: 00:00:00.00
SEIANG@seiang11g>select count(*) from mlog$_t_wjq1;
COUNT(*)
----------
87006
Elapsed: 00:00:00.01
發(fā)現(xiàn)：中間表的數(shù)據(jù)內(nèi)容保持不變，并且物化視圖日志積累了需要刷新的數(shù)據(jù)條目。此時存在數(shù)據(jù)的不一致和不統(tǒng)一。Oracle推薦要求使用sysnc_interim_table方法將重定義過程中出現(xiàn)的變化數(shù)據(jù)刷新。
--刷新8萬多條數(shù)據(jù)，使用了超過四分鐘時間。在這個過程中，我們可以看到刷新物化視圖過程。
SEIANG@seiang11g>exec dbms_redefinition.sync_interim_table('SEIANG','T_WJQ1','T_WJQ1_INTERIM');
PL/SQL procedure successfully completed.
Elapsed: 00:04:18.33
SEIANG@seiang11g>select * from v$mvrefresh;
SID SERIAL# CURRMVOWNER CURRMVNAME
---------- ---------- ------------------------------- -------------------------------
41 14059 SEIANG T_WJQ1_INTERIM
--刷新開始和結(jié)束過程，我們可以看到物化視圖刷新過程中的時間變化。
SEIANG@seiang11g>alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
Session altered.
SEIANG@seiang11g>
SEIANG@seiang11g>select name, LAST_REFRESH from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
T_WJQ1_INTERIM 2017-09-06 13:59:57
SEIANG@seiang11g>select name, LAST_REFRESH from user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ -------------------
T_WJQ1_INTERIM 2017-09-06 14:22:20
--刷新結(jié)束后，發(fā)現(xiàn)t_wjq1_interim表和mlog$_t_wjq1日志表數(shù)據(jù)的變化
SEIANG@seiang11g>select count(*) from t_wjq1_interim;
COUNT(*)
----------
174003
SEIANG@seiang11g>
SEIANG@seiang11g>select count(*) from t_wjq1;
COUNT(*)
----------
174003
SEIANG@seiang11g>
SEIANG@seiang11g>select count(*) from mlog$_t_wjq1;
COUNT(*)
----------
0
說明無變化數(shù)據(jù)需要刷新了
綜合上面的實驗，知道方法sync_interim_table的實質(zhì)是進(jìn)行一次物化視圖快速刷新。這個方法持續(xù)的時間根據(jù)不同數(shù)據(jù)量和物化視圖刷新算法來決定，這個過程中，并不會引起很多鎖定動作。而且，在線重定義過程中，這個方法是可以重復(fù)執(zhí)行多次的。
--下面將原有數(shù)據(jù)表中的約束關(guān)系刷新到目標(biāo)結(jié)構(gòu)上
SEIANG@seiang11g>declare
2 error_count number:=0;
3 begin
4 dbms_redefinition.copy_table_dependents(uname => 'SEIANG',orig_table => 'T_WJQ1',int_table => 'T_WJQ1_INTERIM',
5 copy_indexes => dbms_redefinition.cons_orig_params,
6 num_errors => error_count);
7 dbms_output.put_line(to_char(error_count));
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.29
SEIANG@seiang11g>select index_name,index_type,table_owner,table_name from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME
------------------------------ --------------------------- ------------------------------ ------------------------------
TMP$$_PK_T_WJQ1_ID0 NORMAL SEIANG T_WJQ1_INTERIM
PK_T_WJQ1_ID NORMAL SEIANG T_WJQ1
I_MLOG$_T_WJQ1 NORMAL SEIANG MLOG$_T_WJQ1
Elapsed: 00:00:00.04
SEIANG@seiang11g>select master,log_table from user_mview_logs;
MASTER LOG_TABLE
------------------------------ ------------------------------
T_WJQ1 MLOG$_T_WJQ1
Finish過程主要完成六個步驟操作：
（1）執(zhí)行sysnc_interim_table命令，將中間表數(shù)據(jù)盡可能靠近源數(shù)據(jù)表；
（2）鎖定源數(shù)據(jù)表T，使之后不能有任何變化發(fā)生在這個數(shù)據(jù)表上；
（3）再次執(zhí)行sysnc_interim_table命令，這個時候執(zhí)行的時間不會很長；
（4）將源數(shù)據(jù)表和Interim數(shù)據(jù)表表名進(jìn)行置換；
（5）注銷unregistered物化視圖，并且刪除掉物化視圖日志；
（6）釋放開在中間表上的鎖定；
SEIANG@seiang11g> exec dbms_redefinition.finish_redef_table('SEIANG','T_WJQ1','T_WJQ1_INTERIM');
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.11
SEIANG@seiang11g>select master,log_table from user_mview_logs;
no rows selected
Elapsed: 00:00:00.02
SEIANG@seiang11g>
SEIANG@seiang11g>select * from mlog$_t_wjq1;
select * from mlog$_t_wjq1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SEIANG@seiang11g>select index_name,index_type,table_owner,table_name from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME
------------------------------ --------------------------- ------------------------------ ------------------------------
TMP$$_PK_T_WJQ1_ID0 NORMAL SEIANG T_WJQ1_INTERIM
PK_T_WJQ1_ID NORMAL SEIANG T_WJQ1
--檢查重定義的結(jié)果
SEIANG@seiang11g>desc t_wjq1
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OBJECT_ID NUMBER
OBJECT_NAME_2 VARCHAR2(128)
CREATED TIMESTAMP(6)
SEIANG@seiang11g>
SEIANG@seiang11g>desc t_wjq1_interim
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OBJECT_ID NOT NULL NUMBER
OBJECT_NAME VARCHAR2(128)
CREATED DATE
SEIANG@seiang11g> exec dbms_stats.gather_table_stats(user,'T_WJQ1',cascade => true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.76
--分區(qū)和主鍵對象實現(xiàn)成功
SEIANG@seiang11g>select table_name,partition_name from user_tab_partitions;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T_WJQ1 P1
T_WJQ1 P2
T_WJQ1 P3
T_WJQ1 P4
SEIANG@seiang11g>select constraint_name,constraint_type,table_name from user_constraints;
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
TMP$$_PK_T_WJQ1_ID0 P T_WJQ1_INTERIM
PK_T_WJQ1_ID P T_WJQ1
之前的實驗都是在單表情況下進(jìn)行的在線重定義操作，但是，在實際的生產(chǎn)環(huán)境中，通常涉及到多表關(guān)系，例如外鍵關(guān)系表下的重定義，那有該如何處理呢？接下來的案例，作者將介紹多表關(guān)系下的在線重定義。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼
