ORACLE向外导数据工具,需要放在ORACLE服务器机器上运行,效率特别高,谁用谁知道、、、、
https://download.csdn.net/download/xfg0218/10619175
在环境标量中加入以下配置即可
export LD_LIBRARY_PATH=$ORACLE_HOME/bin:$ORACLE_HOME/lib:/lib:/usr/lib
$ ./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
$ 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
$ 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
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