-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
来源: http://www.linuxidc.com/Linux/2018-01/150388.htm