分別對應(yīng)32為，64位的win和Linux平臺(tái)

安裝步驟

1.需要安裝oracle_client

2.復(fù)制sqluldr2_linux32_10204.bin（64位系統(tǒng)用sqluldr2_linux64_10204.bin）到$ORACLE_HOME的bin目錄，重命名為sqluldr2.bin

3.修改 $HOME目錄下.bash_profile 增加如下環(huán)境變量

export?LD_LIBRARY_PATH=$ORACLE_HOME/bin:$ORACLE_HOME/lib:/lib:/usr/lib ?

4.驗(yàn)證

-bash-3.2$ sqluldr2.bin


SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1
(@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.


License: Free for non-commercial useage, else 100 USD per server.


Usage: SQLULDR2 keyword=value [,keyword=value,...]


Valid Keywords:
? ?user ? ?= username/password@tnsname
? ?sql ? ? = SQL file name
? ?query ? = select statement
? ?field ? = separator string between fields
? ?record ?= separator string between records
? ?rows ? ?= print progress for every given rows (default, 1000000)?
? ?file ? ?= output file name(default: uldrdata.txt)
? ?log ? ? = log file name, prefix with + to append mode
? ?fast ? ?= auto tuning the session level parameters(YES)
? ?text ? ?= output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
? ?charset = character set name of the target database.
? ?ncharset= national character set name of the target database.
? ?parfile = read command option from parameter file?


? for field and record, you can use '0x' to specify hex character code,
? \r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 '=0x27?

LINUX下用法：

get_tables.sql為預(yù)定義導(dǎo)出sql

-bash-3.2$ sqluldr2.bin USER=userid/keyword@db_name sql=get_tables.sql head=no FILE=/oracle/oradata/backup/stock.csv
? ? ? ? ? ?0 rows exported at 2014-05-06 11:49:25, size 0 MB.
? ? ?1000000 rows exported at 2014-05-06 11:49:28, size 24 MB.
? ? ?2000000 rows exported at 2014-05-06 11:49:32, size 48 MB.
? ? ?3000000 rows exported at 2014-05-06 11:49:35, size 76 MB.
? ? ?4000000 rows exported at 2014-05-06 11:49:38, size 100 MB.
? ? ?5000000 rows exported at 2014-05-06 11:49:41, size 124 MB.
? ? ?6000000 rows exported at 2014-05-06 11:49:44, size 152 MB.
? ? ?7000000 rows exported at 2014-05-06 11:49:47, size 176 MB.
? ? ?8000000 rows exported at 2014-05-06 11:49:50, size 204 MB.
? ? ?9000000 rows exported at 2014-05-06 11:49:53, size 228 MB.
? ? 10000000 rows exported at 2014-05-06 11:49:56, size 252 MB.
? ? 11000000 rows exported at 2014-05-06 11:49:59, size 280 MB.
? ? 12000000 rows exported at 2014-05-06 11:50:02, size 304 MB.
? ? 13000000 rows exported at 2014-05-06 11:50:05, size 328 MB.
? ? 14000000 rows exported at 2014-05-06 11:50:08, size 356 MB.
? ? 15000000 rows exported at 2014-05-06 11:50:11, size 380 MB.
? ? 16000000 rows exported at 2014-05-06 11:50:14, size 408 MB.
? ? 17000000 rows exported at 2014-05-06 11:50:17, size 432 MB.
? ? 18000000 rows exported at 2014-05-06 11:50:20, size 456 MB.
? ? 19000000 rows exported at 2014-05-06 11:50:23, size 484 MB.
? ? 20000000 rows exported at 2014-05-06 11:50:27, size 508 MB.
? ? 21000000 rows exported at 2014-05-06 11:50:30, size 531 MB.
? ? 21877116 rows exported at 2014-05-06 11:50:33, size 558 MB.
? ? ? ? ?output file /oracle/oradata/backup/f_stock.csv closed at 21877116 rows, size 558 MB.
-bash-3.2$?

下為WINDOWS平臺(tái)使用方法：

代碼例子1：

sqluldr2.exe?USER=用戶/密碼@tnsQUERY="select?? /*+ parallel(8)? */? *from cs_XXX dt,cfg_XXX devdim105 wheredt.starttime>=to_date('2012-06-27 00:00:00','yyyy-mm-dd hh24:mi:ss') and dt.starttime<=to_date('2012-06-2700:01:59','yyyy-mm-dd hh24:mi:ss') AND dt.msc= devdim105.mapvalue(+) ANDdevdim105.deviceid=15 " head=yes FILE=F:\cs_XXX_test.csv

?

代碼例子2(這種方式用于導(dǎo)出的查詢sql很長，那么就把sql寫在123.sql文件里 )：

sqluldr2.exe USER=用戶/密碼@tns??sql=123.sql head=yes FILE=F:\cs_XXX_test.csv

?

?

3.在cmd里，直接把步驟2的代碼例子1?，貼進(jìn)去執(zhí)行。

?

注：如果執(zhí)行報(bào)報(bào)錯(cuò)，就有可能是環(huán)境變量path的問題，還有就是plsql需要的oci.dll文件等多個(gè)dll文件，請放在sqluldr2的目錄下。

?

4.查看結(jié)果F:\cs_XXX_test.csv

?

5.打開csv里面的內(nèi)容，就可以用sqlldr進(jìn)行 入庫。

字符集的控制：

? 默認(rèn)情況下, Oracle客戶端程序的字符集都由環(huán)境變量(NLS_LANG)決定, 很多時(shí)侯會(huì)因?yàn)楹雎粤谁h(huán)境變量的檢查, 尤其是在Shell或Perl腳本中調(diào)用時(shí), 導(dǎo)致導(dǎo)出的數(shù)據(jù)變成了亂碼. 在OCI 8的接口中, 可以使用OCIEnvNlsCreate函數(shù)在程序中指定客戶端字符集, 從而跳過環(huán)境變量的影響, 于是就在SQLULDR2工具中新增了兩個(gè)選項(xiàng), 用于指定導(dǎo)出時(shí)了字符集.

? ? 新的SQLULDR2工具命令行幫助如下所示:

SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1
(@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.

Usage: SQLULDR2 keyword=value [,keyword=value,...]

Valid Keywords:
? user? ? = username/password@tnsname
? sql? ? = SQL file name
? query? = select statement
? field? = separator string between fields
? record? = separator string between records
? rows? ? = print progress for every given rows (default, 1000000)
? file? ? = output file name(default: uldrdata.txt)
? log? ? = log file name, prefix with + to append mode
? fast? ? = auto tuning the session level parameters(YES)
? text? ? = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
? charset = character set name of the target database.
? ncharset= national character set name of the target database.
? parfile = read command option from parameter file

? for field and record, you can use '0x' to specify hex character code,
? \r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 '=0x27

? ? 在一個(gè)GBK的數(shù)據(jù)庫中存入"中國"兩個(gè)字, 然后以GBK和UTF8兩種不同的字符集進(jìn)行導(dǎo)出, 在命令行指定字符集設(shè)置.

C:\>sqluldr2 scott/tiger query=test charset=ZHS16GBK
? ? ? 0 rows exported at 2010-06-24 09:12:27, size 0 MB.
? ? ? 1 rows exported at 2010-06-24 09:12:27, size 0 MB.
? ? ? ? output file uldrdata.1.txt closed at 1 rows, size 0 MB.

C:\>dir uldrdata.1.txt
2010-06-24? 09:12? ? ? ? ? ? ? ? 5 uldrdata.1.txt

C:\>sqluldr2 scott/tiger query=test? charset=UTF8
? ? ? 0 rows exported at 2010-06-24 09:12:42, size 0 MB.
? ? ? 1 rows exported at 2010-06-24 09:12:42, size 0 MB.
? ? ? ? output file uldrdata.1.txt closed at 1 rows, size 0 MB.

C:\>dir uldrdata.1.txt
2010-06-24? 09:12? ? ? ? ? ? ? ? 7 uldrdata.1.txt

? ? 用GBK導(dǎo)出時(shí), 文件大小為5個(gè)字節(jié), 兩個(gè)漢字(一個(gè)漢字占兩個(gè)字節(jié))再加一個(gè)換行符. 而用UTF8導(dǎo)出時(shí), 文件大小為7, 兩個(gè)漢字(一個(gè)漢字占三個(gè)字節(jié))加上一個(gè)換行符. 如果用SQLULDR2為特定的數(shù)據(jù)庫寫腳本程序時(shí), 在命令行指定字符集, 將使腳本更加強(qiáng)壯.

來自 “ ITPUB博客 ” ，鏈接：http://blog.itpub.net/28291944/viewspace-2151281/，如需轉(zhuǎn)載，請注明出處，否則將追究法律責(zé)任。

轉(zhuǎn)載于:http://blog.itpub.net/28291944/viewspace-2151281/

總結(jié)

以上是生活随笔為你收集整理的sqluldr2用法的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。