共計 7699 個字符，預計需要花費 20 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 oracle 如何實現在線重定義與普通表改為分區表，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！
一、什么是在線重定義
要了解什么是在線重定義技術，我想從表分區開始說起。在生產系統運維過程中，經常遇到的一個需求是如何把一個數據量非常大的普通表改造成分區表。分區最早在 oracle8.0 版本引入，支持將一個表或索引物理地分解為多個更小、更可管理的部分。
以下是在線重定義表的一些功能：
1. 修改表的存儲參數；
2. 可以將表轉移到其他表空間；
3. 增加并行查詢選項；
4. 增加或刪除分區；
5. 重建表以減少碎片；
6. 將堆表改為索引組織表或相反的操作；
7. 增加或刪除一個列。
在線重定義好處：
– 提高數據的可用性
– 數據段變得更小，減輕了管理的負擔
– 改善某些查詢的性能
– 將數據修改分布到多個單獨的分區上，減少競爭
分區表在各行業的數據庫都得到廣泛應用，但是有些業務系統在設計階段對系統數據和性能容量增長估計不足，或沒有考慮到運維過程中的數據歸檔需求，往往沒有對表做分區設計。在生產運行經過長時間的數據積累之后，才發現表越來越大，某些查詢或插入數據的性能變得越來越慢，迫切需要做表分區改造。
那么問題來了，業務系統往往都是 7 *24 在線作業，改造的過程又必然涉及表結構的變動，如果對表進行重建，會對系統運行產生非常大的影響，通常會設置計劃停機窗口來做這類維護操作。
當然，分區表的改造只是諸多數據重組織或重定義場景中的一種，在數據變動需求越來越多、越來越復雜，而系統停機的成本又顯著升高的背景下，從 Oracle 8i 開始就設計了有限的在線重新組織數據的功能，例如 create indexes online, rebuilding indexes online。并在 9i 進一步擴展這方面的能力，引入了數據在線重定義。
在線重定義技術允許數據庫管理員在該表上有讀寫數據操作的情況下，非常靈活地修改表的物理屬性、表數據、表結構。
二、在線重定義的使用場景
有以下變更需求時，都可以考慮使用在線重定義技術，這些場景也是運維過程中經常遇到的：
– 修改表的物理屬性、存儲參數
– 將表遷移到別的表空間
– 消除表碎片、釋放空間
– 在表中增加、刪除或重命名字段
– 大批量改變表中的數據
三、在線重定義的實現原理
oracle 提供了一個 dbms_redefinition 包用于在線重定義操作，主要包含如下三個過程：
dbms_redefinition.start_redef_table
這個過程首先會創建一個快速刷新的物化視圖作為過渡表，然后將源表的數據加載到過渡表中，并在源表上創建物化視圖日志，以支持快速刷新同步數據。
dbms_redefinition.sync_interim_table
用來把源表中的數據同步到過渡表。
dbms_redefinition.finish_redef_table
這個過程的操作步驟比較多，也是做在線重定義時需要特別注意的，但其執行時間通常是非常短的：
1）先調用一次 dbms_redefinition.sync_interim_table，同步數據。
2）鎖定源表，鎖定之后表數據不再允許發生變化。
3）再調用一次 dbms_redefinition.sync_interim_table，同步數據。
4）交換源表和過渡表的表名。
5）刪除物化視圖和物化視圖日志。
6）釋放表鎖資源。
四、實驗將普通表改造成分區表
下面我們通過實際案例來應用這項技術，本次實踐中我們要弄清楚幾個問題：
a. 在線重定義的操作過程。
b. 將一個 2000 萬數據量的表進行重定義，需要多長時間。
c. 在線重定義期間，表相關的操作是否受影響，又是如何影響的。
1. 檢查用戶權限
運行 dbms_redefinition 包需要以下權限：
– execute privilege to dbms_redefinition
– create any table
– alter any table
– drop any table
– lock any table
– select any table
– create any index
– create any trigger
grant execute on dbms_redefinition to SCOTT;
grant create any table to SCOTT;
grant alter any table to SCOTT;
grant drop any table to SCOTT;
grant lock any table to SCOTT;
grant select any table to SCOTT;
grant create any index to SCOTT;
grant create any trigger to SCOTT;
可進入用戶后執行以下 SQL 進行檢查確認：
select * from session_privs;
2. 實驗創建一個源表，并插入數據
create table unpar_table(id number(10),create_date date,name varchar2(100),up_date date);
– 使用批量綁定技術插入 200 萬數據初始測試數據
declare
type t_mid is table of unpar_table%rowtype index by binary_integer;
l_tab_mid t_mid;
begin
for i in 1 .. 2000000 loop
l_tab_mid(i).id := i;
l_tab_mid(i).create_date := sysdate;
l_tab_mid(i).name := lpad(a , 100, a
l_tab_mid(i).up_date := sysdate;
end loop;
forall i in 1 .. l_tab_mid.count
insert into unpar_table values l_tab_mid (i);
commit;
end;
/
– 給表 unpar_table 增加主鍵約束及建索引
alter table unpar_table add (constraint unpar_table_pk primary key (id));
create index create_date_ind on unpar_table(create_date);
注意：在線重定義方法。存在兩種重定義方法，一種是基于主鍵、另一種是基于 ROWID。ROWID 的方式不能用于索引組織表，而且重定義后會存在隱藏列 M_ROW$$。默認采用主鍵的方式。(重定義的表如果基于主鍵，則必須要有主鍵才能進行重定義)
– 收集統計信息
exec dbms_stats.gather_table_stats(ownname = SCOTT ,TABNAME = UNPAR_TABLE ,cascade = true);
3. 按需求創建一個已分區的中間表
create table par_table(id number(10),create_date date,name varchar2(100),up_date date)
partition by range(create_date)
(
partition unpar_table_1 values less than (to_date( 01/01/2012 , DD/MM/YYYY)),
partition unpar_table_2 values less than (to_date( 01/01/2013 , DD/MM/YYYY)),
partition unpar_table_3 values less than (to_date( 01/01/2014 , DD/MM/YYYY)),
partition unpar_table_4 values less than (to_date( 01/01/2015 , DD/MM/YYYY)),
partition unpar_table_5 values less than (to_date( 01/01/2016 , DD/MM/YYYY)),
partition unpar_table_6 values less than (to_date( 01/01/2017 , DD/MM/YYYY)),
partition unpar_table_7 values less than (maxvalue)
);
以上步驟完成準備工作，開始執行在線重定義過程。
4. 檢查源表是否具備在線重定義的條件
exec dbms_redefinition.can_redef_table(SCOTT , UNPAR_TABLE
– 檢查耗時
SQL exec dbms_redefinition.can_redef_table(SCOTT , UNPAR_TABLE
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.17
5. 開始在線重定義，這一步相當于初始化工作，耗時比較長
exec dbms_redefinition.start_redef_table(SCOTT , UNPAR_TABLE , PAR_TABLE
– 檢查耗時
SQL exec dbms_redefinition.start_redef_table(SCOTT , UNPAR_TABLE , PAR_TABLE
PL/SQL procedure successfully completed.
Elapsed: 00:00:43.29
6. 在中間表上創建約束和索引并收集統計信息
這一步提前做，可以防止重定義完成后，新表沒有可用索引，而產生性能問題。
oracle 提供了 dbms_redefinition.copy_table_dependents 過程，用于復制源表上的索引、約束、觸發器、權限等依賴關系到中間表，但是這個包存在的 BUG 也不少，可以選擇性使用。
alter table par_table add (constraint unpar_table_pk2 primary key (id));
– 耗時:Elapsed: 00:00:08.93
create index create_date_ind2 on par_table(create_date);
– 耗時:Elapsed: 00:00:10.07
exec dbms_stats.gather_table_stats(ownname = SCOTT ,TABNAME = PAR_TABLE ,cascade = true);
– 耗時:Elapsed: 00:00:02.89
注意：
如果在執行 DBMS_REDEFINITION.START_REDEF_TABLE() 過程和執行 DBMS_REDEFINITION.FINISH_REDEF_TABLE() 過程直接在重定義表上執行了大量的 DML 操作，那么可以選擇執行一次或多次的 SYNC_INTERIM_TABLE() 過程，以減少最后一步執行 FINISH_REDEF_TABLE() 過程時的鎖定時間。
7. 手工同步數據，將上一步執行中將產生的數據先做同步刷新
exec dbms_redefinition.sync_interim_table(SCOTT , UNPAR_TABLE , PAR_TABLE
– 檢查耗時
SQL exec dbms_redefinition.sync_interim_table(SCOTT , UNPAR_TABLE , PAR_TABLE
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.22
8. 完成在線重定義過程：執行后，中間表和源表的表名互換
exec dbms_redefinition.finish_redef_table(SCOTT , UNPAR_TABLE , PAR_TABLE
– 檢查耗時
SQL exec dbms_redefinition.finish_redef_table(SCOTT , UNPAR_TABLE , PAR_TABLE
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.64
9. 刪除中間表，并將索引重命名回來
此時的中間表已經是原來未分區的普通表，而源表已經變成了分區表
– 先檢查分區表及普通表情況
select table_name,partition_name,num_rows from user_tab_partitions where table_name like %PAR_TABLE%
select a.segment_name,a.segment_type,sum(a.bytes/1024/1024) size_m from user_segments a where a.segment_name like %PAR_TABLE% group by a.segment_name,a.segment_type;
select table_name,index_name,status from user_indexes where table_name like %PAR_TABLE%
SQL select table_name,partition_name,num_rows from user_tab_partitions where table_name like %PAR_TABLE%
TABLE_NAME PARTITION_NAME NUM_ROWS
—————————— —————————— ———-
UNPAR_TABLE UNPAR_TABLE_7 2000000
UNPAR_TABLE UNPAR_TABLE_6 0
UNPAR_TABLE UNPAR_TABLE_5 0
UNPAR_TABLE UNPAR_TABLE_4 0
UNPAR_TABLE UNPAR_TABLE_3 0
UNPAR_TABLE UNPAR_TABLE_2 0
UNPAR_TABLE UNPAR_TABLE_1 0
7 rows selected.
SQL select a.segment_name,a.segment_type,sum(a.bytes/1024/1024) size_m from user_segments a where a.segment_name like %PAR_TABLE% group by a.segment_name,a.segment_type;
SEGMENT_NAME SEGMENT_TYPE SIZE_M
————— —————— ———-
UNPAR_TABLE TABLE PARTITION 288
PAR_TABLE TABLE 280
UNPAR_TABLE_PK INDEX 35
UNPAR_TABLE_PK2 INDEX 44
SQL select table_name,index_name,status from user_indexes where table_name like %PAR_TABLE%
TABLE_NAME INDEX_NAME STATUS
—————————— —————————— ——–
UNPAR_TABLE CREATE_DATE_IND2 VALID
UNPAR_TABLE UNPAR_TABLE_PK2 VALID
PAR_TABLE CREATE_DATE_IND VALID
PAR_TABLE UNPAR_TABLE_PK VALID
– 操作
drop table par_table purge;
alter table unpar_table rename constraint unpar_table_pk2 to unpar_table_pk;
alter index unpar_table_pk2 rename to unpar_table_pk;
alter index create_date_ind2 rename to create_date_ind;
– 驗證查詢
SQL select table_name,index_name,status from user_indexes where table_name like %PAR_TABLE%
TABLE_NAME INDEX_NAME STATUS
—————————— —————————— ——–
UNPAR_TABLE CREATE_DATE_IND VALID
UNPAR_TABLE UNPAR_TABLE_PK VALID
SQL select a.segment_name,a.segment_type,sum(a.bytes/1024/1024) size_m from user_segments a where a.segment_name like %PAR_TABLE% group by a.segment_name,a.segment_type;
SEGMENT_NAME SEGMENT_TYPE SIZE_M
————— —————— ———-
UNPAR_TABLE TABLE PARTITION 288
UNPAR_TABLE_PK INDEX 44
至此，使用在線重定義進行表分區改造的工作已經完成。
五、在線重定義需注意的問題
使用在線重定義技術，以下情況是需要注意的：
– 如果離線操作能夠解決問題，就不要用在線重定義例如一些靜態數據、歷史數據的歸檔遷移，可使用 CTAS、alter table move、或導出導入完成
– 表空間至少要留有比源表所用空間更大的剩余空間
– 在線重定義的操作過程耗時較長，但對業務的影響最小
– 要注意源表上的事務操作，如果過于頻繁，可能會發生較嚴重的等待
看完了這篇文章，相信你對“oracle 如何實現在線重定義與普通表改為分區表”有了一定的了解，如果想了解更多相關知識，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！
