編者按

雲和恩墨大講堂社群（本文底部有入群方式）裡有人提出一個需求：一張表資料量很大，如何只匯出其中一部分列？雲和恩墨CTO、Oracle ACE總監、ACOUG核心專家楊廷琨老師使用了資料泵的方式，細緻入微地解釋了過程並給出具體的程式碼實現。資料和雲（ID：OraNews）獨家釋出，以饗讀者。

以下為正文

無論是老版本exp還是資料泵expdp，Oracle都提供了QUERY的功能，這使得查詢表中部分記錄的功能可以實現，但是QUERY只能過濾行，而不能過濾列，Oracle資料泵會讀取表中全部列的。

在12c中，Oracle為資料泵提供了VIEW功能，使得匯出的時候可以根據檢視的定義來匯出表中的資料：

SQL>select banner from v$version; 
BANNER--------------------------------------------------------------------------------
OracleDatabase 18c Enterprise Edition Release 18.0.0.0.0 - Production 
SQL>CREATE TABLE T_TABLES AS SELECT * FROM ALL_TABLES;

（左右滑動檢視程式碼部分，下同）

表已建立。

SQL>create view v_tables as select owner, table_name, tablespace_name from t_tableswhere owner not in ('SYS');

檢視已建立。

利用資料泵的VIEWS_AS_TABLES引數可以直接匯出檢視對應的表資料：

C:\Users\yangt>expdpc##u1 directory=d_output dumpfile=t_tab_view views_as_tables=v_tables 
 
Export:Release 18.0.0.0.0 - Production on 星期二 2月 26 13:37:312019

Version18.3.0.0.0

Copyright(c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

口令:連線到: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 -Production

警告: 連線到容器資料庫的根或種子時通常不需要 Oracle Data Pump 操作。

啟動 "C##U1"."SYS_EXPORT_TABLE_01":  c##u1/******** 
directory=d_outputdumpfile=t_tab_view views_as_tables=v_tables
處理物件型別 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
處理物件型別 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . 匯出了"C##U1"."V_TABLES"                          
8.289 KB      71 行
已成功載入/解除安裝了主表"C##U1"."SYS_EXPORT_TABLE_01"
******************************************************************************
C##U1.SYS_EXPORT_TABLE_01的轉儲檔案集為: 
D:\TEMP\T_TAB_VIEW.DMP
作業 "C##U1"."SYS_EXPORT_TABLE_01" 已於 星期二 2月 26 13:37:40 2019 elapsed 0 00:00:06 成功完成

雖然是透過檢視匯出的，但是資料泵把它當作一個表來處理，因此這個dump檔案是可以直接匯入到資料庫中變成表的：

SQL>drop view v_tables;

檢視已刪除。

完成匯入操作：

C:\Users\yangt>impdpc##u1 directory=d_output dumpfile=t_tab_view full=y 
Import:Release 18.0.0.0.0 - Production on 星期二 2月 26 14:33:022019
Version18.3.0.0.0 Copyright(c) 1982, 2018, Oracle and/or its affiliates. 
All rights reserved.
口令: 
連線到: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 -Production

警告: 連線到容器資料庫的根或種子時通常不需要 Oracle Data Pump 操作。

已成功載入/解除安裝了主表"C##U1"."SYS_IMPORT_FULL_01"
啟動 "C##U1"."SYS_IMPORT_FULL_01":  c##u1/******** directory=d_outputdumpfile=t_tab_view full=y
處理物件型別 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE處理物件型別 TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . 匯入了"C##U1"."V_TABLES"                          
8.289 KB      71 行
作業 "C##U1"."SYS_IMPORT_FULL_01" 已於 星期二 2月 26 14:33:07 2019 elapsed 0 00:00:02 成功完成

查詢表資料：

SQL>select count(*) from v_tables;
   
COUNT(*)
----------        
71 
SQL>select table_name, tablespace_name from user_tables where table_name ='V_TABLES'; 
TABLE_NAME                     
TABLESPACE_NAME------------------------------------------------------------
V_TABLES                       USERS

這個功能是12c的特新特，如果是12c之前的版本，可以考慮使用ORACLE_DATAPUMP型別的外部表來實現：

SQL>create table t_external_tables (owner, table_name, tablespace_name)organization external  
2 (type oracle_datapump  
3 default directory d_output  
4 location ('external_table.dp'))  
5  asselect owner, table_name, tablespace_name from t_tables where owner not in('SYS');

表已建立。

透過將外部表匯出的 external_table.dp 檔案複製到目標環境對應的目錄下，根據上面的定義重建外部表，注意目標環境建立過程由於已經複製了匯出的資料，因此語法截至到 location 語句就結束了，不要再包含 as select 部分。

SQL>create table t_target_ext_tab (owner varchar2(30), table_name varchar2(128),tablespace_name varchar2(30))  
2 organization external  
3 (type oracle_datapump  
4 default directory d_output  5 location ('external_table.dp'));

表已建立。

SQL>select count(*) from t_target_ext_tab;   
COUNT(*)----------        
71 
SQL>col table_name for a30SQL>select * from t_target_ext_tab where owner = 'SYSTEM'; 
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
SYSTEM                         OL$SYSTEM                         OL$HINTS
SYSTEM                         OL$NODES
SYSTEM                         HELP                    
       SYSTEM

所以對於11g和10g的版本，也可以不透過新建表，然後複製資料的方式來實現資料的匯出和轉移。但是如果是9i及以前的版本，那麼要不然透過建表然後exp匯出的方式，要不然自己寫程式實現資料的匯出吧。

細緻入微：如何使用資料泵匯出表的部分列資料

相關文章