前言

最近正在做一個項目，需要導出數(shù)據(jù)庫中的表，但是數(shù)據(jù)庫中有很多帶有虛擬列的表，而且表中的數(shù)據(jù)非常的龐大，有些表中的數(shù)據(jù)上億條，項目經(jīng)理讓我研究如何快速導出這些數(shù)據(jù)。

下面是我研究的一些經(jīng)歷：

（1）、我先使用plsql developer導出dmp（實際上是通過emp導出），但是不能導出帶有虛擬列的表，導出的速度有點慢；

（2）、使用plsql developer自帶的導出功能，如圖所示：

該方法可以導出虛擬列，但是導出的速度很慢，比dmp還慢，大約是方法（1）的2倍時間。

（3）、使用數(shù)據(jù)泵  DataPump導出，該方法可以導出虛擬列，而且速度還可以，但是如果導出遠程庫數(shù)據(jù)的時候，需要用dblink，而且需要很高的權限（相當于dba的權限），所以該方法也被排除。

山重水復疑無路，柳暗花明又一村，我發(fā)現(xiàn)了sqluldr2這個神器，又能導出虛擬列，而且導入導出的速度非?？欤旅嫖覀兙瓦M入正題。

sqluldr2下載與安裝

1、軟件下載地址：

百度云鏈接：https://pan.baidu.com/s/1V8eqyyYsbJqQSD-Sn-RQGg

下載完后并解壓會生成4個文件

sqluldr2.exe  用于32位windows平臺； 

sqluldr2_linux32_10204.bin  適用于linux32位操作系統(tǒng)；

sqluldr2_linux64_10204.bin  適用于linux64位操作系統(tǒng)；

sqluldr264.exe             用于64位windows平臺。

2、直接在cmd上運行（我的sqluldr文件放在H盤里，我電腦是64位，所以使用sqluldr264）

首先，你必須安裝oracle，沒有安裝oracle，sqluldr2不能運行，運行完后出現(xiàn)如下的界面，這樣就證明可以成功運行。

sqluldr2 導出

1、導出命令的主要參數(shù)

user=用戶名/密碼@ip地址:1521/服務   ,如果是本地庫，可以只寫 用戶名和密碼：eg：user=用戶名/密碼

query=”sql查詢語句”

head=yes|no  是否導出表頭

file=文件存放路徑（該文件可以寫很多后綴: .txt  .csv   .dmp 等等，我發(fā)現(xiàn)，導出.dmp文件速度快）

table=查詢的表名 有這句話，sqluldr2會自動生成一個.ctl文件，導入的時候會用到（）;

Field：分隔符，指定字段分隔符，默認為逗號； 比如：field=#  在選擇分隔符時，一定不能選擇會在字段值中出現(xiàn)的字符組合，如常見的單詞等，很多次導入時報錯，回過頭來找原因時，都發(fā)現(xiàn)是因為分隔符出現(xiàn)在字段值中了。

record：分隔符，指定記錄分隔符，默認為回車換行，Windows下的換行；

quote：引號符，指定非數(shù)字字段前后的引號符；

charset：字符集，執(zhí)行導出時的字符集，一般有UTF8、GBK等；

2、常規(guī)的命令

sqluldr264 user=zxx/zxx123@127.0.0.1:1521/orcl query="select * from mv_xlsymx1 where ysyddm='00001H'"  head=yes file=h:\mx.csv  log=+h:\tem.log

3、可以使用sql參數(shù)

可以使用sql參數(shù)代替query

sqluldr264 user=zxx/zxx  sql=h:\test.sql head=yes file=h:\mx.csv 

test.sql是提前維護好的一個文件，文件的內(nèi)容為sql語句。

4、帶有table參數(shù)的導出

sqluldr264 user=zxx/zxx query="select * from mv_xlsymx1 where ysyddm='00001H'" table=mv_xlsymx1  head=yes file=h:\mx.csv

它會生成一個.ctl文件(mv_xlsymx1_sqlldr.ctl,默認生成在sqluldr文件下，我的就生成在h:\sqluldr\ mv_xlsymx1_sqlldr.ctl)

5、指定.ctl文件生成的位置

sqluldr264 user=zxx/zxx  query="select * from mv_xlsymx1 where ysyddm='00001H'" table=mv_xlsymx1  control=h:\mx.ctl head=yes file=h:\mx.csv

6、帶有日志log參數(shù)

當集成sqluldr2在腳本中時，就希望屏蔽上不輸出這些信息，但又希望這些信息能保留，這時可以用“LOG”選項來指定日志文件名。

sqluldr264 user=zxx/zxx query="select * from mv_xlsymx1 where ysyddm='00001H'"  head=yes file=h:\mx.csv  log=+h:\tmp.log

注意：這里的log路徑要寫上“+”

sqlldr 導入

1、我們先查看sqlldr的幫助文檔

2、導入之前，我們需要先熟悉一下.ctl文件

characterset ：字符集， 一般使用字符集 AL32UTF8，如果出現(xiàn)中文字符集亂碼時，改成 ZHS16GBK。

fields terminated by 'string'：文本列分隔符。當為tab鍵時，改成'\t'，或者 X'09'；空格分隔符 whitespace，換行分隔符 '\n' 或者 X'0A'；回車分隔符 '\r' 或者 X'0D'；默認為'\t'。

optionally enclosed by 'char'：字段包括符。當為 ' ' 時，不把字段包括在任何引號符號中；當為 "'" 時，字段包括在單引號中；當為'"'時，字段在包括雙引號中；默認不使用引用符。

fields escaped by 'char'：轉義字符，默認為'\'。

trailing nullcols：表字段沒有對應的值時，允許為空。

append into table "T_USER_CTRL"  -- 操作類型

　　-- 1) insert into     --為缺省方式，在數(shù)據(jù)裝載開始時要求表為空

　　-- 2) append into  --在表中追加新記錄

　　-- 3) replace into  --刪除舊記錄(相當于delete from table 語句)，替換成新裝載的記錄

　　-- 4) truncate  into  --刪除舊記錄(相當于 truncate table 語句)，替換成新裝載的記錄skip=1 ：表示插入數(shù)據(jù)時，跳過第一行(標題)，從第二行開始導入；

 3、sqluldr 導入處理

3.1、基本的導入語句

sqlldr userid=hxj/hxj control=h:\sqluldr\mv_xlsymx1_sqlldr.ctl data=h:\mx.csv rows=1000

 如果是本地庫，可以直接只用 用戶名/密碼；

如果是遠程庫，需要將userid寫全    userid=用戶名/密碼@ip:1521/服務名

比如：userid=zxx/zxx123@10.3.36.110:1521/orcl，填寫自己遠程庫地址

3.2、帶有日志log參數(shù)

sqlldr userid=hxj/hxj control=h:\sqluldr\mv_xlsymx1_sqlldr.ctl data=h:\mx.csv log=h:\log\mx.log  rows=1000

注意：這里的log的路徑不能寫“+”;

4、虛擬列處理

sqluldr2導出數(shù)據(jù)的時候，如果該表中含有虛擬列，你導出的時候沒有過濾掉虛擬列，比如：select * from 帶有虛擬列的表,那么你要對這些虛擬列進行處理，否則導入的時候回報錯。

我發(fā)現(xiàn)了三種處理方法：

4.1、在虛擬列后面加上filler，將這一列過濾掉。

4.2、將.ctl文件中的虛擬列刪除掉就可以了

4.3、在導出的時候，不導出虛擬列

比如，不寫select * from 表名

直接將不是虛擬列的列名寫出來 select id,name from 表名

 5、使用并行處理

 5.1 未使用并行處理

sqlldr userid=hxj/hxj control=h:\ctl\qsddlqymx1_cyqs.ctl data=h:\qsddlqymx1_cyqs.dmp log=h:\log\qsddlqymx1_cyqs.log

1567258條數(shù)據(jù)大概需要 一分半

5.2、使用并行處理數(shù)據(jù)

需要在導入語句中加入  direct=true   parallel=true，如下所示：

sqlldr userid=hxj/hxj control=h:\ctl\qsddlqymx1_cyqs.ctl data=h:\qsddlqymx1_cyqs.dmp log=h:\log\qsddlqymx1_cyqs.log direct=true   parallel=true

并行能更快的導入數(shù)據(jù)，1567258條數(shù)據(jù)大概20秒，但是有缺點（我測試的時候發(fā)現(xiàn)的，可能有別的解決方法）

（1）：首先.ctl文件必須是append into table 表名；

（2）：需要導入的表不能有索引。

這是我寫的第一篇博客，望看客老爺們多多指教。