Oracle抽数据神器sqluldr2linux64

• 1、软件下载
• 2、软件配置
• 3、查看导数据的效率
  • 3.1  查看sqluldr2linux64帮助信息
  • 3.2 执行导数据程序
  • 3.3 查看导出字段的信息
  • 3.4 查看数据的字段

1、软件下载

ORACLE向外导数据工具，需要放在ORACLE服务器机器上运行，效率特别高，谁用谁知道、、、、

https://download.csdn.net/download/xfg0218/10619175

或

https://download.csdn.net/download/edgeman03/9593193

2、软件配置

在环境标量中加入以下配置即可

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

3、查看导数据的效率

3.1  查看sqluldr2linux64帮助信息

$ ./sqluldr2linux64.bin --help

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

 

field 可以使用多个16进制的分隔符，例如: field=0x260x260x260x260x26

3.2 执行导数据程序

$ time ./sqluldr2linux64.bin user=etl/Li3sg71P@192.168.201.211:1521/pdbetl sql=/home/xiaoxu/oracle-sqluldr2-export/table-sql/mv_e_inv_investment/mv_e_inv_investment.sql head=no charset=utf8 field=0x260x260x260x260x26 FILE=/data/mv_e_inv_investment/mv_e_inv_investment_20180814.csv
0 rows exported at 2018-08-22 10:28:16, size 0 MB.
1000000 rows exported at 2018-08-22 10:28:44, size 456 MB.
2000000 rows exported at 2018-08-22 10:29:10, size 919 MB.
3000000 rows exported at 2018-08-22 10:29:37, size 1383 MB.
4000000 rows exported at 2018-08-22 10:30:04, size 1846 MB.
5000000 rows exported at 2018-08-22 10:30:31, size 2310 MB.
6000000 rows exported at 2018-08-22 10:30:58, size 2773 MB.
7000000 rows exported at 2018-08-22 10:31:25, size 3237 MB.
8000000 rows exported at 2018-08-22 10:31:52, size 3693 MB.
9000000 rows exported at 2018-08-22 10:32:18, size 4156 MB.
********************************

99000000 rows exported at 2018-08-22 11:10:49, size 43142 MB.
100000000 rows exported at 2018-08-22 11:11:13, size 43546 MB.
101000000 rows exported at 2018-08-22 11:11:38, size 43954 MB.
102000000 rows exported at 2018-08-22 11:12:03, size 44369 MB.
102248943 rows exported at 2018-08-22 11:12:09, size 44474 MB.
output file /data/mv_e_inv_investment/mv_e_inv_investment_20180814.csv closed at 102248943 rows, size 44474 MB.

real 43m53.119s
user 22m47.441s
sys 1m23.190s

在以上可以看出100W行显示一次输出结果，每次的大概用时30s,导出来的数据大概为450M，导出44G大概需要43m

注意字符的编码是utf8，而不是utf-8

$ du -sh /data/mv_e_inv_investment/mv_e_inv_investment_20180814.csv
44G /data/mv_e_inv_investment/mv_e_inv_investment_20180814.csv

3.3 查看导出字段的信息

$ tail -n 1 mv_e_inv_investment_20180814.csv 
410000&&&&&6BA93FC563E17EC0E0531ECDA8C08080&&&&&3637ee2606df2344e1711d120b8befd6&&&&&王发力&&&&&20&&&&&P01&&&&&&&&&&&&&&&&&&&&&&&&&.01&&&&&0&&&&&0&&&&&0&&&&&0&&&&&&&&&&2018-05-16 00:00:00&&&&&1900-01-01 00:00:0

3.4 查看数据的字段

s_ext_nodenum,pripid,invid,inv,invtype,certype,blictype,blicno,country,currency,subconam,acconam,subconamusd,acconamusd,

conprop,conform,condate,baldelper,conam,exeaffsign,s_ext_timestamp,s_ext_batch,s_ext_validflag,linkman,zspid,s_ext_sequence,jobid,ent_id,typedisplay,handle_type