-
-rwxrwxrwx 1 oracle oinstall 185894 1月 27 2016 sqluldr2linux64.bin
直接執行就能顯示幫助
-
[oracle@hecomoracleserver2 sqluldr2]$ ./sqluldr2linux64.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
上面的參數中，比較常用的是：
query：用這個參數可以自定義導出語句
file：導出的文件名字和目錄
log：日志
parfile：可以把參數放到parfile文件里，這個參數對于復雜sql很有用。
field:設置導出文件里的分隔符
連接數據的方法：
本地連接方式：
-
./sqluldr2linux64.bin user=gltest/gltest query="select * from T_H_BK_CUSTOMER_20180114_0;" file=/home/oracle/sqluldr2/T_H_BK_CUSTOMER_20180114_0.csv
客戶端連接：TNS方式
-
./sqluldr2linux64.bin user=gltest/gltest@orcl query="select * from T_H_BK_CUSTOMER_20180114_0;" file=/home/oracle/sqluldr2/T_H_BK_CUSTOMER_20180114_0.csv
客戶端連接：直接連接方式
-
./sqluldr2linux64.bin user=gltest/gltest@192.168.128.98:1521/orcl query="select * from T_H_BK_CUSTOMER_20180114_0;" file=/home/oracle/sqluldr2/T_H_BK_CUSTOMER_20180114_0.csv
測試導出速度：
因為我的sql比較長，所以用了parfile:
-
[oracle@gloracleserver2 sqluldr2]$ more customer.txt
-
user=gltest/gltest
-
query=select ent_code,code,name,cust_levels,dept_code,poi_id,status,createon,createby,lastupdateon from v30_md_customer;
看看導出輸出，1100萬數據不到30秒，速度很快。
-
[oracle@gloracleserver2 sqluldr2]$ ./sqluldr2linux64.bin parfile=/home/oracle/sqluldr2/customer.txt file=/home/oracle/sqluldr2/customer.csv
-
0 rows exported at 2018-01-16 20:25:09, size 0 MB.
-
1000000 rows exported at 2018-01-16 20:25:11, size 96 MB.
-
2000000 rows exported at 2018-01-16 20:25:14, size 196 MB.
-
3000000 rows exported at 2018-01-16 20:25:16, size 296 MB.
-
4000000 rows exported at 2018-01-16 20:25:18, size 392 MB.
-
5000000 rows exported at 2018-01-16 20:25:21, size 492 MB.
-
6000000 rows exported at 2018-01-16 20:25:23, size 592 MB.
-
7000000 rows exported at 2018-01-16 20:25:26, size 692 MB.
-
8000000 rows exported at 2018-01-16 20:25:28, size 788 MB.
-
9000000 rows exported at 2018-01-16 20:25:31, size 888 MB.
-
10000000 rows exported at 2018-01-16 20:25:34, size 988 MB.
-
11000000 rows exported at 2018-01-16 20:25:36, size 1088 MB.
-
11317061 rows exported at 2018-01-16 20:25:37, size 1121 MB.
-
output file /home/oracle/sqluldr2/customer.csv closed at 11317061 rows, size 1121 MB.
遇到的錯誤：
1. 導出文件里有中文顯示亂碼，需要設置參數charset=UTF8
2. ORA-24345: A Truncation or null fetch error occurred，設置參數safe=yes
