Oracle抽数据神器sqluldr2linux64

小徐

发布于 2019-08-05 15:10:56

1.8K0

发布于 2019-08-05 15:10:56

文章被收录于专栏：GreenplumGreenplum

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

1、软件下载

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

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

2、软件配置

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

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

3、查看导数据的效率

3.1 查看sqluldr2linux64帮助信息

$ ./sqluldr2linux64.bin --help

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