Shulou(Shulou.com)06/01 Report--

Sqluldr2 is an OCI-based text export tool, compact and easy to use, similar to Oracle's own exp, supporting custom SQL, local and client export, fast and efficient.

Download address, there is a gadget at the bottom

Http://www.onexsoft.com/download.html can also wget directly on the system.

After wget http://www.onexsoft.com/software/sqluldr2linux64.zip is decompressed, there is a bin file.

-rwxrwxrwx 1 oracle oinstall 185894 January 27 2016 sqluldr2linux64.bin direct execution can show help

[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, the more commonly used parameters are:

Query: use this parameter to customize the export statement

File: file name and directory of the export

Log: log

Parfile: you can put the parameter in the parfile file, which is useful for complex sql.

Field: sets the delimiter in the exported file

How to connect to the data:

Local connection method:

. / sqluldr2linux64.bin user=gltest/gltest query= "select * from client connection: TNS mode"

. / sqluldr2linux64.bin user=gltest/gltest@orcl query= "select * from clients HK customers 2018011400;" file=/home/oracle/sqluldr2/T_H_BK_CUSTOMER_20180114_0.csv client connection: direct connection

. / sqluldr2linux64.bin user=gltest/gltest@192.168.128.98:1521/orcl query= "select * from threaded BKS customer 201801140;" file=/home/oracle/sqluldr2/T_H_BK_CUSTOMER_20180114_0.csv

Test the export speed:

Because my sql is long, I use parfile:

[oracle@gloracleserver2 sqluldr2] $more customer.txt

User=gltest/gltestquery=select ent_code,code,name,cust_levels,dept_code,poi_id,status,createon,createby,lastupdateon from v30roommdcustomers; look at the export output. 11 million data is very fast in less than 30 seconds.

[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 2018 MB.

4000000 rows exported at 2018-01-16 20:25:18, size 392MB.

5000000 rows exported at 2018-01-16 20:25:21, size 492MB.

6000000 rows exported at 2018-01-16 20:25:23, size 592MB.

7000000 rows exported at 2018-01-16 20:25:26, size 692MB.

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.

Error encountered:

1. Garbled codes are displayed in Chinese in the exported file. You need to set the parameter charset=UTF8.

2. ORA-24345: A Truncation or null fetch error occurred, set parameter safe=yes

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.