sqluldr2的使用方法

有時候需要導出Oracle表數據到文本中用來遷移數據、異構平台或大數據的數據處理。導出方法有很多種，例如：spool、plsql-developer、utl_file方法等，除了這些方法，還有一個工具很火就是sqluldr2，老樓開發的工具。現在對這些工具做個對比和總結。

spool工具在sqlplus中隨時可以使用，較為靈活，通過sqlplus的格式設置處理，字段分割利用sql語句拚成，能迅速導出一些小數據量的需求。因為是配合sqlplus，它可以支持本地和客戶端的數據導出，但是效率不高。

plsql-developer工具隻是個代表，還有很多工具可以連接數據庫，進行導出操作，從交互配置中完成需要導出的數據設置。它也可以支持本地和客戶端的數據導出，效率高於spool。

utl_file方法是oracle提供的文件讀寫包，該方法需要一定的編寫存儲過程和sql的能力，而且需要指定讀寫路徑，因此隻能在服務器本地生成文件，若大量導出，還需要完成文件的傳輸。

老樓開發的軟件，基於OCI，使用非常方便，就是一個可執行文件配合參數命令，類似expdp等的命令行使用方法，支持自定義sql、本地和客戶端的導出，效率非常高。

前三個方法都能輕鬆找到使用方法，本次實測sqluldr2。

下載：https://www.anysql.net/software/sqluldr.zip

上傳解壓即可使用

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

read = set DB_FILE_MULTIBLOCK_READ_COUNT at session level

sort = set SORT_AREA_SIZE at session level (UNIT:MB)

hash = set HASH_AREA_SIZE at session level (UNIT:MB)

array = array fetch size

head = print row header(Yes|No)

batch = save to new file for every rows batch (Yes/No)

size = maximum output file piece size (UNIB:MB)

serial = set _serial_direct_read to TRUE at session level

trace = set event 10046 to given level at session level

table = table name in the sqlldr control file

control = sqlldr control file and path.

mode = sqlldr option, INSERT or APPEND or REPLACE or TRUNCATE

buffer = sqlldr READSIZE and BINDSIZE, default 16 (MB)

long = maximum long field size

width = customized max column width (w1:w2:...)

quote = optional quote string

data = disable real data unload (NO, OFF)

alter = alter session SQLs to be execute before unload

safe = use large buffer to avoid ORA-24345 error (Yes|No)

crypt = encrypted user information only (Yes|No)

sedf/t = enable character translation function

null = replace null with given value

escape = escape character for special characters

escf/t = escape from/to characters list

format = MYSQL: MySQL Insert SQLs, SQL: Insert SQLs.

exec = the command to execute the SQLs.

prehead = column name prefix for head line.

rowpre = row prefix string for each line.

rowsuf = row sufix string for each line.

colsep = separator string between column name and value.

presql = SQL or scripts to be executed before data unload.

postsql = SQL or scripts to be executed after data unload.

lob = extract lob values to single file (FILE).

lobdir = subdirectory count to store lob files .

split = table name for automatically parallelization.

degree = parallelize data copy degree (2-128).

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</span>

本地執行方式：users參數可以省略不寫，和expdp username/passwd 方式一樣

export ORACLE_SID=orcl

sqluldr2 testuser/testuser query=test_table1 file=test_table1.txt

客戶端連接：tns方式

sqluldr2 testuser/testuser@orcl query=test_table1 file=test_table1.txt

客戶端連接：簡易連接

sqluldr2 testuser/testuser@x.x.x.x:1521/orcl query=test_table1 file=test_table1.txt

query參數如果整表導出，可以直接寫表名，如果需要查詢運算和where條件，query=“sql文本”，也可以把複雜sql寫入到文本中由query調用。

默認是逗號分隔符，通過field參數指定分隔符

sqluldr2 testuser/testuser query=chen.tt1 field=";"

對於大表可以輸出到多個文件中，指定行數分割或者按照文件大小分割，例如：

sqluldr2 testuser/testuser@orcl query="select * from test_table2" file=test_table2_%B.txt batch=yes rows=500000

SQL> desc chen.tt1

Name Null? Type

----------------------------------------- -------- ----------------------------

T1 NOT NULL NUMBER(38)

T2 NUMBER(38)

T3 VARCHAR2(30)

SQL> select count(1) from chen.tt1;

COUNT(1)

----------

504403648

[oracle@iZ2zeevsidoafz0w22zmsfZ ~]$ ./sqluldr2linux64.bin Sys/ query=chen.tt1 field=";" file=/data1/tt1_uldr.txt

0 rows exported at 2017-09-17 10:54:32, size 0 MB.

1000000 rows exported at 2017-09-17 10:54:34, size 32 MB.

2000000 rows exported at 2017-09-17 10:54:35, size 68 MB.

3000000 rows exported at 2017-09-17 10:54:37, size 104 MB.

4000000 rows exported at 2017-09-17 10:54:38, size 140 MB.

……

503000000 rows exported at 2017-09-17 11:09:38, size 18036 MB.

504000000 rows exported at 2017-09-17 11:09:39, size 18072 MB.

504780879 rows exported at 2017-09-17 11:10:11, size 18103 MB.

output file /data1/tt1_uldr.txt closed at 504780879 rows, size 18103 MB.

5億條數據，在本地導出16分鍾完成，1秒中100w條數據。

5億條數據，在客戶端導出也是16分鍾完成。

sqluldr2是一個非常靈活高效的文本導出工具，還支持導出時同時生成fsqlldr的控製文件，方便導入，該工具還有很多實用和優化方法，需要進一步研究。