sqluldr导出oracle部分字段,【Oracle】Sqluldr大批量数据导出

Sqluldr2

使用pl/sql导出Excel最多只能导出65535条数据，而且数据导出比较慢；查看全部数据还会出现异常

数据量过大

sqluldr2是oracle数据导出工具，它可以将数据以csv、txt等格式导出，适用于大批量数据的导出，导出速度非常快。导出后可以使用oracle loader工具将数据导入。

sqluldr2下载

命令参数

C:\Users\pengyunlong>C:\Users\pengyunlong\Downloads\sqluldr2\oracle数据快速导出工具\sqluldr2.exe

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 #用户名/密码@数据库IP/实例名

sql = SQL file name #查询数据的sql文件路径

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. #目标数据库的编码UTF8

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

示例

根据查询语句导出

sqluldr2_linux64_10204.bin user="username/pwd"

query="select * from table" field="0x09"

record=0x0a file="/opt/data/table.txt" charset=UTF8

查询语句过长可以将语句写入sql文件中

sql=使用queryMerPSql.sql里面的sql语句来执行查询操作

field=结果字段使用逗号分割，

record=0x0a 行间使用\n分割

file= 导出文件名为6.csv

head=yes 带表头

rows=10000 每隔10000行输出日志

log=+ 输出日志位置

C:\Users\pengyunlong>sqluldr2.exe user="username/password@10.51.**.**/hdb"

sql="C:\Users\pengyunlong\Desktop\导出数据\queryMerPSql.sql" field="0x2c" record=0x0a

file="C:\Users\pengyunlong\Desktop\导出数据\queryMerPSql\6.csv" head=yes rows=10000

log=+C:\Users\pengyunlong\Desktop\导出数据\tmp001.log

查看日志导出日志

0 rows exported at 2018-06-19 16:37:49, size 0 MB.

10000 rows exported at 2018-06-19 16:37:49, size 0 MB.

20000 rows exported at 2018-06-19 16:37:49, size 0 MB.

30000 rows exported at 2018-06-19 16:37:50, size 0 MB.

40000 rows exported at 2018-06-19 16:37:50, size 0 MB.

50000 rows exported at 2018-06-19 16:37:50, size 0 MB.

60000 rows exported at 2018-06-19 16:37:50, size 0 MB.

70000 rows exported at 2018-06-19 16:37:51, size 0 MB.

80000 rows exported at 2018-06-19 16:37:51, size 0 MB.

90000 rows exported at 2018-06-19 16:37:51, size 0 MB.

100000 rows exported at 2018-06-19 16:37:51, size 0 MB.

110000 rows exported at 2018-06-19 16:37:51, size 0 MB.

120000 rows exported at 2018-06-19 16:37:52, size 4 MB.

130000 rows exported at 2018-06-19 16:37:52, size 4 MB.

140000 rows exported at 2018-06-19 16:37:52, size 4 MB.

150000 rows exported at 2018-06-19 16:37:52, size 4 MB.

160000 rows exported at 2018-06-19 16:37:53, size 4 MB.

170000 rows exported at 2018-06-19 16:37:53, size 4 MB.

180000 rows exported at 2018-06-19 16:37:53, size 4 MB.

190000 rows exported at 2018-06-19 16:37:53, size 4 MB.

200000 rows exported at 2018-06-19 16:37:53, size 4 MB.

210000 rows exported at 2018-06-19 16:37:54, size 4 MB.

220000 rows exported at 2018-06-19 16:37:54, size 4 MB.

226482 rows exported at 2018-06-19 16:37:54, size 8 MB.

output file C:\Users\pengyunlong\Desktop\导出数据\queryMerPSql\6.csv

closed at 42295 rows, size 5 MB.