程序師世界是廣大編程愛好者互助、分享、學習的平台，程序師世界有你更精彩！
設為首頁 加入收藏
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle用分區表分區交換做歷史數據遷移

Oracle用分區表分區交換做歷史數據遷移

編輯：Oracle教程

一。說明：

OLTP庫中有些表數據量大，且每月有持續的大量數據增加，由於歷史數據在此庫中不再做訪問，而是在另1個OLAP庫中做分析，所以會對歷史數據遷移至OLAP庫中。對這種歷史數據遷移的操作，較好的辦法是該表采用分區表。按時間分區後，可以對分區進行遷移。通過分區交換和表空間傳輸會很容易完成，而且性能上影響很小。

關於分區表更多內容： http://blog.csdn.net/tanqingru/article/category/1397435
關於表空間傳更多內容： http://blog.csdn.net/tanqingru/article/category/1138527

二。實例：

整個過程是在OLTP庫做分區交換。然後通過表空間傳輸遷移至OLAP庫，最後再做一次分區交換即可。

1.創造需要的環境。

OLTP庫環境准備：

SQL> conn /as sysdba
Connected.
SQL> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /data01/qing


create tablespace tan_2013_9 datafile size 5m autoextend on;
create tablespace tan_2013_10 datafile size 5m autoextend on;
create tablespace tan_2013_11 datafile size 5m autoextend on;
create tablespace tan_2013_12 datafile size 5m autoextend on;
create tablespace tan_2014_1 datafile size 5m autoextend on;
create tablespace tan_2014_2 datafile size 5m autoextend on;
create tablespace tan_2014_3 datafile size 5m autoextend on;
create tablespace tan_2014_4 datafile size 5m autoextend on;
create tablespace tan_2014_5 datafile size 5m autoextend on;
create tablespace tan_2014_6 datafile size 5m autoextend on;
create tablespace tan_2014_7 datafile size 5m autoextend on;
create tablespace tan_2014_8 datafile size 5m autoextend on;

conn tan/tan

SQL> create table tan
(t_id number(10),
t_name varchar2(100),
t_date date )
partition by range(t_date)
(partition tan_2013_9 values less than(to_date('2013-10-01','yyyy-mm-dd')) tablespace tan_2013_9,
partition tan_2013_10 values less than(to_date('2013-11-01','yyyy-mm-dd')) tablespace tan_2013_10,
partition tan_2013_11 values less than(to_date('2013-12-01','yyyy-mm-dd')) tablespace tan_2013_11,
partition tan_2013_12 values less than(to_date('2014-01-01','yyyy-mm-dd')) tablespace tan_2013_12,
partition tan_2014_1 values less than(to_date('2014-02-01','yyyy-mm-dd')) tablespace tan_2014_1,
partition tan_2014_2 values less than(to_date('2014-03-01','yyyy-mm-dd')) tablespace tan_2014_2,
partition tan_2014_3 values less than(to_date('2014-04-01','yyyy-mm-dd')) tablespace tan_2014_3,
partition tan_2014_4 values less than(to_date('2014-05-01','yyyy-mm-dd')) tablespace tan_2014_4,
partition tan_2014_5 values less than(to_date('2014-06-01','yyyy-mm-dd')) tablespace tan_2014_5,
partition tan_2014_6 values less than(to_date('2014-07-01','yyyy-mm-dd')) tablespace tan_2014_6,
partition tan_2014_7 values less than(to_date('2014-08-01','yyyy-mm-dd')) tablespace tan_2014_7,
partition tan_2014_8 values less than(to_date('2014-09-01','yyyy-mm-dd')) tablespace tan_2014_8 
);


create index ind_tan on tan(t_date) local
(
partition ind_tan_2013_9  tablespace tan_2013_9,
partition ind_tan_2013_10 tablespace tan_2013_10,
partition ind_tan_2013_11 tablespace tan_2013_11,
partition ind_tan_2013_12 tablespace tan_2013_12,
partition ind_tan_2014_1  tablespace tan_2014_1,
partition ind_tan_2014_2  tablespace tan_2014_2,
partition ind_tan_2014_3 tablespace tan_2014_3,
partition ind_tan_2014_4 tablespace tan_2014_4,
partition ind_tan_2014_5 tablespace tan_2014_5,
partition ind_tan_2014_6 tablespace tan_2014_6,
partition ind_tan_2014_7 tablespace tan_2014_7,
partition ind_tan_2014_8 tablespace tan_2014_8 
);



begin 
for i in 1.. 10000 loop
if( mod(i,12)+1 <=8) then
insert into tan values(i,'tan'||i,to_date('2014-'||(mod(i,12)+1)||'-01','yyyy-mm-dd'));
else 
insert into tan values(i,'tan'||i,to_date('2013-'||(mod(i,12)+1)||'-01','yyyy-mm-dd'));
end if;
end loop;
commit;
end;
/

SQL> select count(*) from tan partition(tan_2013_12) ;

  COUNT(*)
----------
       833

SQL> select count(*) from tan partition(tan_2013_9) ;

  COUNT(*)
----------
       833

SQL> select count(*) from tan partition(tan_2014_8) ;

  COUNT(*)
----------
       833

SQL> select count(*) from tan partition(tan_2014_1) ;

  COUNT(*)
----------
       833

SQL> select partition_name,tablespace_name from dba_segments
   where segment_name in ('TAN','IND_TAN');

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
TAN_2014_8                     TAN_2014_8
TAN_2014_7                     TAN_2014_7
TAN_2014_6                     TAN_2014_6
TAN_2014_5                     TAN_2014_5
TAN_2014_4                     TAN_2014_4
TAN_2014_3                     TAN_2014_3
TAN_2014_2                     TAN_2014_2
TAN_2014_1                     TAN_2014_1
TAN_2013_9                     TAN_2013_9
TAN_2013_12                    TAN_2013_12
TAN_2013_11                    TAN_2013_11
TAN_2013_10                    TAN_2013_10
IND_TAN_2014_8                 TAN_2014_8
IND_TAN_2014_7                 TAN_2014_7
IND_TAN_2014_6                 TAN_2014_6
IND_TAN_2014_5                 TAN_2014_5
IND_TAN_2014_4                 TAN_2014_4
IND_TAN_2014_3                 TAN_2014_3
IND_TAN_2014_2                 TAN_2014_2
IND_TAN_2014_1                 TAN_2014_1
IND_TAN_2013_9                 TAN_2013_9
IND_TAN_2013_12                TAN_2013_12
IND_TAN_2013_11                TAN_2013_11
IND_TAN_2013_10                TAN_2013_10
24 rows selected.

OLAP庫環境准備

<span style="font-family: Arial, Helvetica, sans-serif; font-size: 12px;">create tablespace tan_2013_7 datafile size 5m autoextend on;</span>
create tablespace tan_2013_8 datafile size 5m autoextend on;
 create table tan
(t_id number(10),
t_name varchar2(100),
t_date date )
partition by range(t_date)
(partition tan_2013_7 values less than(to_date('2013-08-01','yyyy-mm-dd')) tablespace tan_2013_7,
partition tan_2013_8 values less than(to_date('2013-09-01','yyyy-mm-dd')) tablespace tan_2013_8
);

create index ind_tan on tan(t_date) local
(
partition ind_tan_2013_7  tablespace tan_2013_7,
partition ind_tan_2013_8 tablespace tan_2013_8
);


begin 
for i in 1.. 10000 loop
insert into tan values(i,'tan'||i,to_date('2013-'||(mod(i,2)+7)||'-01','yyyy-mm-dd'));
end loop;
commit;
end;
/


SQL> select count(*) from tan partition(tan_2013_8);


  COUNT(*)
----------
      5000


SQL> select count(*) from tan partition(tan_2013_7);


  COUNT(*)
----------
      5000

2.分區交換

現在要做的事是遷移2013年9月份數據。

創建個臨時表：
SQL> create table tmp_tan_2013_9 as select * from tan where 1=2;
SQL> create index ind_tmp_tan_2013_9 on tmp_tan_2013_9(t_date);


分區交換。
SQL> alter table tan exchange partition tan_2013_9

with table tmp_tan_2013_9 including indexes with validation;


驗證：
SQL> select count(*) from tan partition(tan_2013_9) ;

  COUNT(*)
----------
         0
SQL> select count(*) from tmp_tan_2013_9;
  COUNT(*)
----------
       833
SQL> select partition_name,tablespace_name from dba_segments
     where segment_name in ('TAN','IND_TAN');


PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
TAN_2014_8                     TAN_2014_8
TAN_2014_7                     TAN_2014_7
TAN_2014_6                     TAN_2014_6
TAN_2014_5                     TAN_2014_5
TAN_2014_4                     TAN_2014_4
TAN_2014_3                     TAN_2014_3
TAN_2014_2                     TAN_2014_2
TAN_2014_1                     TAN_2014_1
TAN_2013_12                    TAN_2013_12
TAN_2013_11                    TAN_2013_11
TAN_2013_10                    TAN_2013_10
IND_TAN_2014_8                 TAN_2014_8
IND_TAN_2014_7                 TAN_2014_7
IND_TAN_2014_6                 TAN_2014_6
IND_TAN_2014_5                 TAN_2014_5
IND_TAN_2014_4                 TAN_2014_4
IND_TAN_2014_3                 TAN_2014_3
IND_TAN_2014_2                 TAN_2014_2
IND_TAN_2014_1                 TAN_2014_1
IND_TAN_2013_12                TAN_2013_12
IND_TAN_2013_11                TAN_2013_11
IND_TAN_2013_10                TAN_2013_10

22 rows selected.

3. 表空間傳輸：

更多關於表空間傳輸的內容：http://blog.csdn.net/bamuta/article/category/1138527

驗證表空間的自包含：
SQL> exec dbms_tts.transport_set_check('TAN_2013_9',TRUE);


PL/SQL procedure successfully completed.


SQL> select * from transport_set_violations;

no rows selected

另外表空間傳輸需要兩端庫的字符集一致。


導出無數據：

SQL> alter tablespace tan_2013_9 read only;

[oracle@OEL63 ~]$ exp \'sys/oracle as sysdba\' tablespaces=tan_2013_9 transport_tablespace=y file=exp_tan_2013_9.dmp

拷貝文件
[oracle@OEL63 ~]$ scp exp_tan_2013_9.dmp 192.168.114.174:/home/oracle/
[oracle@OEL63 ~]$ scp /data01/qing/QING/datafile/o1_mf_tan_2013_9tht2cgh_.dbf 192.168.114.174:/data01/vm603/VM603/datafile/

在目標庫導入元數據
[oracle@vm603 ~]$ imp \'sys/oracle as sysdba\' transport_tablespace=y file=exp_tan_2013_9.dmp log=imp.log tablespaces=tan_2013_9 datafiles='/data01/vm603/VM603/datafile/o1_mf_tan_2013_9tht2cgh_.dbf'


將兩端庫的該表空間read write
SQL> alter tablespace tan_2013_9 read write;

4.目標庫再做分區交換

目標庫對就表增加分區：
SQL> alter table tan add partition tan_2013_9 values less than (to_date('2013-10-01','yyyy-mm-dd')) tablespace tan_2013_9;


Table altered.


SQL> select count(*) from tan partition(tan_2013_9);


COUNT(*)
----------0


在目標庫做一次分區交換。
SQL> alter table tan exchange partition tan_2013_9 with table tmp_tan_2013_9 including indexes with validation;

Table altered.

檢查
SQL>  select count(*) from tan partition(tan_2013_9);


  COUNT(*)
----------
       833


SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;
TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TAN                            TAN_2013_8                     TAN_2013_8
TAN                            TAN_2013_7                     TAN_2013_7
TAN                            TAN_2013_9                     TAN_2013_9


SQL> select index_name,partition_name,tablespace_name from user_ind_partitions;
INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
IND_TAN                        IND_TAN_2013_8                 TAN_2013_8
IND_TAN                        IND_TAN_2013_7                 TAN_2013_7
IND_TAN                        TAN_2013_9                     TAN_2013_9

5.源庫刪掉已經遷移走的分區：

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;


TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TAN                            TAN_2014_3                     TAN_2014_3
TAN                            TAN_2014_2                     TAN_2014_2
TAN                            TAN_2014_4                     TAN_2014_4
TAN                            TAN_2014_5                     TAN_2014_5
TAN                            TAN_2014_6                     TAN_2014_6
TAN                            TAN_2014_7                     TAN_2014_7
TAN                            TAN_2014_8                     TAN_2014_8
TAN                            TAN_2013_10                    TAN_2013_10
TAN                            TAN_2013_11                    TAN_2013_11
TAN                            TAN_2013_12                    TAN_2013_12
TAN                            TAN_2014_1                     TAN_2014_1
TAN                            TAN_2013_9                     USERS


12 rows selected.


SQL> <strong>alter table tan drop partition tan_2013_9;</strong>


Table altered.


SQL>  select table_name,partition_name,tablespace_name from user_tab_partitions;


TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TAN                            TAN_2014_3                     TAN_2014_3
TAN                            TAN_2014_2                     TAN_2014_2
TAN                            TAN_2014_4                     TAN_2014_4
TAN                            TAN_2014_5                     TAN_2014_5
TAN                            TAN_2014_6                     TAN_2014_6
TAN                            TAN_2014_7                     TAN_2014_7
TAN                            TAN_2014_8                     TAN_2014_8
TAN                            TAN_2013_10                    TAN_2013_10
TAN                            TAN_2013_11                    TAN_2013_11
TAN                            TAN_2013_12                    TAN_2013_12
TAN                            TAN_2014_1                     TAN_2014_1

  1. 上一頁:
  2. 下一頁:
Oracle教程
Solaris文件系統管理，solaris系統管理

Solaris文件系統管理，solaris系統管理不同的操作
解決PLSQL報錯及配置InstantClient方法，plsqlinstantclient

解決PLSQL報錯及配置InstantClient方法，pl
linux/centosoracle10g控制連接IP地址

linux/centosoracle10g控制連接IP地址

Oracle數據庫安裝圖解和基本命令行的使用

 　　Oracle數據庫安裝圖解和基本命令行的使用
OracleORA-06502:PL/SQL:數字或值錯誤:字符串緩沖區太小

OracleORA-06502:PL/SQL:數字或值錯誤:
Oraclepurge用法介紹

1、用途： 清除oracle 回收站(recycl
相關文章
閱讀排行榜
ORA-16032:parameterLOG_ARCHIVE_DEST_3destinationstringcannotbetranslated問題處理過程 Oracle 11g 服務器安裝圖解，oracle11g Oracle12cRAC搭建手冊 Oracle單實例情況下的librarycachepin的問題模擬與問題分析 關於oracle sql語句查詢時表名和字段名要加雙引號的問題詳解 Oracle創建表空間和用戶，oracle創建表 Oracle數據關聯查詢，Oracle數據關聯 ORA-00845-MEMORY_TARGETnotsupportedonthissystem Oracle遠程連接數據庫解決方案 ORA-00911:無效字符 Oracle表字段的增刪改、表的重命名及主鍵的增刪改，oracle重命名
熱門圖文
mysql由淺入深探究（一）----數據庫簡介與mysql安裝，mysql由淺入深 解決MySQL數據庫中文模糊檢索問題 30萬條數據快速檢索的T-SQL. 使用DRBD+Heartbeat+Replication實現MYSQL高可用性 關於MYSQL 遠程登錄的授權方法 命令 mysql數據庫同步詳解 Oracle性能分析4：數據訪問方法之全掃描 pymssql examples，pymssql
欄目導航
關於Oracle數據庫Oracle數據庫基礎Oracle教程
Copyright © 程式師世界 All Rights Reserved