小編給大家分享一下oracle如何實(shí)現(xiàn)在線重定義與普通表改為分區(qū)表，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！
申扎網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián),申扎網(wǎng)站設(shè)計(jì)制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為申扎千余家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\外貿(mào)網(wǎng)站建設(shè)要多少錢，請(qǐng)找那個(gè)售后服務(wù)好的申扎做網(wǎng)站的公司定做！
一、什么是在線重定義
要了解什么是在線重定義技術(shù)，我想從表分區(qū)開(kāi)始說(shuō)起。在生產(chǎn)系統(tǒng)運(yùn)維過(guò)程中，經(jīng)常遇到的一個(gè)需求是如何把一個(gè)數(shù)據(jù)量非常大的普通表改造成分區(qū)表。分區(qū)最早在oracle8.0版本引入，支持將一個(gè)表或索引物理地分解為多個(gè)更小、更可管理的部分。
以下是在線重定義表的一些功能：
1.修改表的存儲(chǔ)參數(shù)；
2.可以將表轉(zhuǎn)移到其他表空間；
3.增加并行查詢選項(xiàng)；
4.增加或刪除分區(qū)；
5.重建表以減少碎片；
6.將堆表改為索引組織表或相反的操作；
7.增加或刪除一個(gè)列。
在線重定義好處：
- 提高數(shù)據(jù)的可用性
- 數(shù)據(jù)段變得更小，減輕了管理的負(fù)擔(dān)
- 改善某些查詢的性能
- 將數(shù)據(jù)修改分布到多個(gè)單獨(dú)的分區(qū)上，減少競(jìng)爭(zhēng)
分區(qū)表在各行業(yè)的數(shù)據(jù)庫(kù)都得到廣泛應(yīng)用，但是有些業(yè)務(wù)系統(tǒng)在設(shè)計(jì)階段對(duì)系統(tǒng)數(shù)據(jù)和性能容量增長(zhǎng)估計(jì)不足，或沒(méi)有考慮到運(yùn)維過(guò)程中的數(shù)據(jù)歸檔需求，往往沒(méi)有對(duì)表做分區(qū)設(shè)計(jì)。在生產(chǎn)運(yùn)行經(jīng)過(guò)長(zhǎng)時(shí)間的數(shù)據(jù)積累之后，才發(fā)現(xiàn)表越來(lái)越大，某些查詢或插入數(shù)據(jù)的性能變得越來(lái)越慢，迫切需要做表分區(qū)改造。
那么問(wèn)題來(lái)了，業(yè)務(wù)系統(tǒng)往往都是7*24在線作業(yè)，改造的過(guò)程又必然涉及表結(jié)構(gòu)的變動(dòng)，如果對(duì)表進(jìn)行重建，會(huì)對(duì)系統(tǒng)運(yùn)行產(chǎn)生非常大的影響，通常會(huì)設(shè)置計(jì)劃停機(jī)窗口來(lái)做這類維護(hù)操作。
當(dāng)然，分區(qū)表的改造只是諸多數(shù)據(jù)重組織或重定義場(chǎng)景中的一種，在數(shù)據(jù)變動(dòng)需求越來(lái)越多、越來(lái)越復(fù)雜，而系統(tǒng)停機(jī)的成本又顯著升高的背景下，從Oracle 8i開(kāi)始就設(shè)計(jì)了有限的在線重新組織數(shù)據(jù)的功能，例如create indexes online, rebuilding indexes online。并在9i進(jìn)一步擴(kuò)展這方面的能力，引入了數(shù)據(jù)在線重定義。
在線重定義技術(shù)允許數(shù)據(jù)庫(kù)管理員在該表上有讀寫(xiě)數(shù)據(jù)操作的情況下，非常靈活地修改表的物理屬性、表數(shù)據(jù)、表結(jié)構(gòu)。
二、在線重定義的使用場(chǎng)景
有以下變更需求時(shí)，都可以考慮使用在線重定義技術(shù)，這些場(chǎng)景也是運(yùn)維過(guò)程中經(jīng)常遇到的：
- 修改表的物理屬性、存儲(chǔ)參數(shù)
- 將表遷移到別的表空間
- 消除表碎片、釋放空間
- 在表中增加、刪除或重命名字段
- 大批量改變表中的數(shù)據(jù)
三、在線重定義的實(shí)現(xiàn)原理
oracle提供了一個(gè)dbms_redefinition包用于在線重定義操作，主要包含如下三個(gè)過(guò)程：
dbms_redefinition.start_redef_table
這個(gè)過(guò)程首先會(huì)創(chuàng)建一個(gè)快速刷新的物化視圖作為過(guò)渡表，然后將源表的數(shù)據(jù)加載到過(guò)渡表中，并在源表上創(chuàng)建物化視圖日志，以支持快速刷新同步數(shù)據(jù)。
dbms_redefinition.sync_interim_table
用來(lái)把源表中的數(shù)據(jù)同步到過(guò)渡表。
dbms_redefinition.finish_redef_table
這個(gè)過(guò)程的操作步驟比較多，也是做在線重定義時(shí)需要特別注意的，但其執(zhí)行時(shí)間通常是非常短的：
1）先調(diào)用一次dbms_redefinition.sync_interim_table，同步數(shù)據(jù)。
2）鎖定源表，鎖定之后表數(shù)據(jù)不再允許發(fā)生變化。
3）再調(diào)用一次dbms_redefinition.sync_interim_table，同步數(shù)據(jù)。
4）交換源表和過(guò)渡表的表名。
5）刪除物化視圖和物化視圖日志。
6）釋放表鎖資源。
四、實(shí)驗(yàn)將普通表改造成分區(qū)表
下面我們通過(guò)實(shí)際案例來(lái)應(yīng)用這項(xiàng)技術(shù)，本次實(shí)踐中我們要弄清楚幾個(gè)問(wèn)題：
a.在線重定義的操作過(guò)程。
b.將一個(gè)2000萬(wàn)數(shù)據(jù)量的表進(jìn)行重定義，需要多長(zhǎng)時(shí)間。
c.在線重定義期間，表相關(guān)的操作是否受影響，又是如何影響的。
1. 檢查用戶權(quán)限
運(yùn)行dbms_redefinition包需要以下權(quán)限：
- 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;
可進(jìn)入用戶后執(zhí)行以下SQL進(jìn)行檢查確認(rèn)：
select * from session_privs;
2.實(shí)驗(yàn)創(chuàng)建一個(gè)源表，并插入數(shù)據(jù)
create table unpar_table(id number(10),create_date date,name varchar2(100),up_date date);
--使用批量綁定技術(shù)插入200萬(wàn)數(shù)據(jù)初始測(cè)試數(shù)據(jù)
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的方式不能用于索引組織表，而且重定義后會(huì)存在隱藏列M_ROW$$。默認(rèn)采用主鍵的方式。(重定義的表如果基于主鍵，則必須要有主鍵才能進(jìn)行重定義)
--收集統(tǒng)計(jì)信息
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',TABNAME => 'UNPAR_TABLE',cascade => true);
3.按需求創(chuàng)建一個(gè)已分區(qū)的中間表
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)
);
以上步驟完成準(zhǔn)備工作，開(kāi)始執(zhí)行在線重定義過(guò)程。
4.檢查源表是否具備在線重定義的條件
exec dbms_redefinition.can_redef_table('SCOTT','UNPAR_TABLE');
--檢查耗時(shí)
SQL> exec dbms_redefinition.can_redef_table('SCOTT','UNPAR_TABLE');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.17
5.開(kāi)始在線重定義，這一步相當(dāng)于初始化工作，耗時(shí)比較長(zhǎng)
exec dbms_redefinition.start_redef_table('SCOTT','UNPAR_TABLE','PAR_TABLE');
--檢查耗時(shí)
SQL> exec dbms_redefinition.start_redef_table('SCOTT','UNPAR_TABLE','PAR_TABLE');
PL/SQL procedure successfully completed.
Elapsed: 00:00:43.29
6.在中間表上創(chuàng)建約束和索引并收集統(tǒng)計(jì)信息
這一步提前做，可以防止重定義完成后，新表沒(méi)有可用索引，而產(chǎn)生性能問(wèn)題。
oracle提供了dbms_redefinition.copy_table_dependents過(guò)程，用于復(fù)制源表上的索引、約束、觸發(fā)器、權(quán)限等依賴關(guān)系到中間表，但是這個(gè)包存在的BUG也不少，可以選擇性使用。
alter table par_table add (constraint unpar_table_pk2 primary key (id));
--耗時(shí):Elapsed: 00:00:08.93
create index create_date_ind2 on par_table(create_date);
--耗時(shí):Elapsed: 00:00:10.07
exec dbms_stats.gather_table_stats(ownname => 'SCOTT',TABNAME => 'PAR_TABLE',cascade => true);
--耗時(shí):Elapsed: 00:00:02.89
注意：
如果在執(zhí)行DBMS_REDEFINITION.START_REDEF_TABLE()過(guò)程和執(zhí)行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過(guò)程直接在重定義表上執(zhí)行了大量的DML操作，那么可以選擇執(zhí)行一次或多次的SYNC_INTERIM_TABLE()過(guò)程，以減少最后一步執(zhí)行FINISH_REDEF_TABLE()過(guò)程時(shí)的鎖定時(shí)間。
7.手工同步數(shù)據(jù)，將上一步執(zhí)行中將產(chǎn)生的數(shù)據(jù)先做同步刷新
exec dbms_redefinition.sync_interim_table('SCOTT','UNPAR_TABLE','PAR_TABLE');
--檢查耗時(shí)
SQL> exec dbms_redefinition.sync_interim_table('SCOTT','UNPAR_TABLE','PAR_TABLE');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.22
8.完成在線重定義過(guò)程：執(zhí)行后，中間表和源表的表名互換
exec dbms_redefinition.finish_redef_table('SCOTT','UNPAR_TABLE','PAR_TABLE');
--檢查耗時(shí)
SQL> exec dbms_redefinition.finish_redef_table('SCOTT','UNPAR_TABLE','PAR_TABLE');
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.64
9.刪除中間表，并將索引重命名回來(lái)
此時(shí)的中間表已經(jīng)是原來(lái)未分區(qū)的普通表，而源表已經(jīng)變成了分區(qū)表
--先檢查分區(qū)表及普通表情況
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;
--驗(yàn)證查詢
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
至此，使用在線重定義進(jìn)行表分區(qū)改造的工作已經(jīng)完成。
五、在線重定義需注意的問(wèn)題
使用在線重定義技術(shù)，以下情況是需要注意的：
- 如果離線操作能夠解決問(wèn)題，就不要用在線重定義例如一些靜態(tài)數(shù)據(jù)、歷史數(shù)據(jù)的歸檔遷移，可使用CTAS、alter table move、或?qū)С鰧?dǎo)入完成
- 表空間至少要留有比源表所用空間更大的剩余空間
- 在線重定義的操作過(guò)程耗時(shí)較長(zhǎng)，但對(duì)業(yè)務(wù)的影響最小
- 要注意源表上的事務(wù)操作，如果過(guò)于頻繁，可能會(huì)發(fā)生較嚴(yán)重的等待
看完了這篇文章，相信你對(duì)“oracle如何實(shí)現(xiàn)在線重定義與普通表改為分區(qū)表”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！
重慶分公司
重慶分公司
