一個利用傳輸表空間和分割槽交換技術進行資料遷移的案例

1. 案例背景
Product：生產交易資料庫，存放當月交易資料。其中，關鍵交易表按天分割槽。
DW：交易歷史資料庫（資料倉儲），存放歷史交易資料，其中，關鍵交易表是按月分割槽。
功能需求：在每個月末，將Product的交易表資料全部移植到DW中。
2. 實施過程
--Product中關鍵交易表Sales的定義如下：
create table sales (invoice_no number, sale_year int, sale_month int, sale_day int)
    partition by range (sale_day)
         (partition p1 values less than (2) tablespace ts_p1,
          partition p2 values less than (3) tablespace ts_p2,
          partition p3 values less than (4) tablespace ts_p3,
          ... ...
          partition p31 values less than (32) tablespace ts_p31);
即每天的交易資料都單獨放在一個partition和表空間中。
--DW中關鍵交易表Sales的定義如下：
create table sales_history (invoice_no number, sale_year int, sale_month int, sale_day int)
    partition by range (sale_year,sale_month,sale_day)
         (partition jan2010 values less than (2010,2,1) tablespace ts_jan2010,
          partition feb2010 values less than (2010,3,1) tablespace ts_feb2010,
          partition mar2010 values less than (2010,4,1) tablespace ts_mar2010,
          ... ...
          partition sep2010 values less than (2010,10,1) tablespace ts_sep2010);
即每月的交易資料都單獨放在一個partition和表空間中。
--在DW中用於分割槽交換的臨時表tmp定義如下：
create table tmp(invoice_no number, sale_year int, sale_month int, sale_day int)
--在每個月的月末，進行如下操作：
1）檢查表空間的自包含性
在Product資料庫中，以sys使用者執行：
execute dbms_tts.transport_set_check('ts_p1','ts_p2',...,'ts_p31',TRUE);
完成上述操作之後，再查詢以下檢視：
select * from transport_set_violations;
如果沒有返回值，則表示表空間是自包含的。
2）將表空間設為只讀
alter tablespace ts_p1 read only;
......
alter tablespace ts_p31 read only;
3）用exp生成傳輸表空間的dmp檔案：
transport_tablespace = y
tablespaces = (ts_p1,......,ts_p31)
trigger=y
constrains=n
grants=n
file=tts.dmp
4）傳輸dmp檔案和資料檔案
把上一步生成的tts.dmp及表空間對應的所有資料檔案都傳輸到DW伺服器上。
5）將Product的表空間恢復為讀寫模式
alter tablespace ts_p1 read write;
......
alter tablespace ts_p31 read write;
6）用imp匯入dmp檔案
transport_tablespace = y
datafiles=('/db/p1.dbf',......)
tts_owner = (...)
fromuser = (...)
touser=(...)
file=tts.dmp
7）將DW中sales分割槽表合併為一個分割槽 p1
alter table sales merge partitions p31,p30 into partition p30;
......
alter table sales merge partitions p2,p1 into partition p1;
8）將sales表交換至臨時表tmp中
alter table sales exchange partition p1 with table tmp;
9）在DW中為sales_history新增一個新的當月的partition
alter table sales_history add partition oct2010 values less than (2010,11,1) tablespace ts_oct2010);
10) 將臨時表tmp交換至sales_history新新增的partition中
alter table sales_history exchange partition oct2010 with table tmp;
11）後續工作
刪除臨時表，刪除過期的表空間等。
3. 方案評估
在整個資料遷移的過程中，最耗時間的操作時檔案傳輸過程，取決於網路寬頻。而在資料庫層面，所有的操作幾乎都是資料字典的操作，非常的高效。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26277071/viewspace-713905/，如需轉載，請註明出處，否則將追究法律責任。