oracle如何實現在線重定義與普通表改為分區表

小編給大家分享一下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如何實現在線重定義與普通表改為分區表”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！