oracle如何實(shí)現(xiàn)在線重定義與普通表改為分區(qū)表

小編給大家分享一下oracle如何實(shí)現(xiàn)在線重定義與普通表改為分區(qū)表，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

一、什么是在線重定義

要了解什么是在線重定義技術(shù)，我想從表分區(qū)開始說起。在生產(chǎn)系統(tǒng)運(yùn)維過程中，經(jīng)常遇到的一個需求是如何把一個數(shù)據(jù)量非常大的普通表改造成分區(qū)表。分區(qū)最早在oracle8.0版本引入，支持將一個表或索引物理地分解為多個更小、更可管理的部分。

以下是在線重定義表的一些功能：

1.修改表的存儲參數(shù)；

2.可以將表轉(zhuǎn)移到其他表空間；

3.增加并行查詢選項(xiàng)；

4.增加或刪除分區(qū)；

5.重建表以減少碎片；

6.將堆表改為索引組織表或相反的操作；

7.增加或刪除一個列。

在線重定義好處：

- 提高數(shù)據(jù)的可用性

- 數(shù)據(jù)段變得更小，減輕了管理的負(fù)擔(dān)

- 改善某些查詢的性能

- 將數(shù)據(jù)修改分布到多個單獨(dú)的分區(qū)上，減少競爭

分區(qū)表在各行業(yè)的數(shù)據(jù)庫都得到廣泛應(yīng)用，但是有些業(yè)務(wù)系統(tǒng)在設(shè)計(jì)階段對系統(tǒng)數(shù)據(jù)和性能容量增長估計(jì)不足，或沒有考慮到運(yùn)維過程中的數(shù)據(jù)歸檔需求，往往沒有對表做分區(qū)設(shè)計(jì)。在生產(chǎn)運(yùn)行經(jīng)過長時間的數(shù)據(jù)積累之后，才發(fā)現(xiàn)表越來越大，某些查詢或插入數(shù)據(jù)的性能變得越來越慢，迫切需要做表分區(qū)改造。

那么問題來了，業(yè)務(wù)系統(tǒng)往往都是7*24在線作業(yè)，改造的過程又必然涉及表結(jié)構(gòu)的變動，如果對表進(jìn)行重建，會對系統(tǒng)運(yùn)行產(chǎn)生非常大的影響，通常會設(shè)置計(jì)劃停機(jī)窗口來做這類維護(hù)操作。

當(dāng)然，分區(qū)表的改造只是諸多數(shù)據(jù)重組織或重定義場景中的一種，在數(shù)據(jù)變動需求越來越多、越來越復(fù)雜，而系統(tǒng)停機(jī)的成本又顯著升高的背景下，從Oracle 8i開始就設(shè)計(jì)了有限的在線重新組織數(shù)據(jù)的功能，例如create indexes online, rebuilding indexes online。并在9i進(jìn)一步擴(kuò)展這方面的能力，引入了數(shù)據(jù)在線重定義。

在線重定義技術(shù)允許數(shù)據(jù)庫管理員在該表上有讀寫數(shù)據(jù)操作的情況下，非常靈活地修改表的物理屬性、表數(shù)據(jù)、表結(jié)構(gòu)。

二、在線重定義的使用場景

有以下變更需求時，都可以考慮使用在線重定義技術(shù)，這些場景也是運(yùn)維過程中經(jīng)常遇到的：

 - 修改表的物理屬性、存儲參數(shù)

 - 將表遷移到別的表空間

 - 消除表碎片、釋放空間

 - 在表中增加、刪除或重命名字段

 - 大批量改變表中的數(shù)據(jù)

三、在線重定義的實(shí)現(xiàn)原理

oracle提供了一個dbms_redefinition包用于在線重定義操作，主要包含如下三個過程：

dbms_redefinition.start_redef_table 

這個過程首先會創(chuàng)建一個快速刷新的物化視圖作為過渡表，然后將源表的數(shù)據(jù)加載到過渡表中，并在源表上創(chuàng)建物化視圖日志，以支持快速刷新同步數(shù)據(jù)。

dbms_redefinition.sync_interim_table

用來把源表中的數(shù)據(jù)同步到過渡表。

dbms_redefinition.finish_redef_table

這個過程的操作步驟比較多，也是做在線重定義時需要特別注意的，但其執(zhí)行時間通常是非常短的：

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）交換源表和過渡表的表名。

5）刪除物化視圖和物化視圖日志。

6）釋放表鎖資源。

四、實(shí)驗(yàn)將普通表改造成分區(qū)表

下面我們通過實(shí)際案例來應(yīng)用這項(xiàng)技術(shù)，本次實(shí)踐中我們要弄清楚幾個問題：

a.在線重定義的操作過程。

b.將一個2000萬數(shù)據(jù)量的表進(jìn)行重定義，需要多長時間。

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)建一個源表，并插入數(shù)據(jù)

create table unpar_table(id number(10),create_date date,name varchar2(100),up_date date);

--使用批量綁定技術(shù)插入200萬數(shù)據(jù)初始測試數(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的方式不能用于索引組織表，而且重定義后會存在隱藏列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)建一個已分區(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)備工作，開始執(zhí)行在線重定義過程。

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.開始在線重定義，這一步相當(dāng)于初始化工作，耗時比較長

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.在中間表上創(chuàng)建約束和索引并收集統(tǒng)計(jì)信息

這一步提前做，可以防止重定義完成后，新表沒有可用索引，而產(chǎn)生性能問題。

oracle提供了dbms_redefinition.copy_table_dependents過程，用于復(fù)制源表上的索引、約束、觸發(fā)器、權(quán)限等依賴關(guān)系到中間表，但是這個包存在的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

注意：

如果在執(zhí)行DBMS_REDEFINITION.START_REDEF_TABLE()過程和執(zhí)行DBMS_REDEFINITION.FINISH_REDEF_TABLE()過程直接在重定義表上執(zhí)行了大量的DML操作，那么可以選擇執(zhí)行一次或多次的SYNC_INTERIM_TABLE()過程，以減少最后一步執(zhí)行FINISH_REDEF_TABLE()過程時的鎖定時間。

7.手工同步數(shù)據(jù)，將上一步執(zhí)行中將產(chǎn)生的數(shù)據(jù)先做同步刷新

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.完成在線重定義過程：執(zhí)行后，中間表和源表的表名互換

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.刪除中間表，并將索引重命名回來

此時的中間表已經(jīng)是原來未分區(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)完成。

五、在線重定義需注意的問題

使用在線重定義技術(shù)，以下情況是需要注意的：

 - 如果離線操作能夠解決問題，就不要用在線重定義例如一些靜態(tài)數(shù)據(jù)、歷史數(shù)據(jù)的歸檔遷移，可使用CTAS、alter table move、或?qū)С鰧?dǎo)入完成

 - 表空間至少要留有比源表所用空間更大的剩余空間

 - 在線重定義的操作過程耗時較長，但對業(yè)務(wù)的影響最小

 - 要注意源表上的事務(wù)操作，如果過于頻繁，可能會發(fā)生較嚴(yán)重的等待

看完了這篇文章，相信你對“oracle如何實(shí)現(xiàn)在線重定義與普通表改為分區(qū)表”有了一定的了解，如果想了解更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！