將ociuldr.exe復制到H:\oracle\product\10.2.0\db_1\BIN下, 或者path中的某個文件夾中
用法:
C:\Documents and Settings\tgm>ociuldr
Usage: ociuldr user=... query=... field=... record=... file=...
(@) Copyright Lou Fangxin 2004/2005, all rights reserved.
Notes:
-si = enable logon as SYSDBA
user = username/password@tnsname
sql = SQL file name,one sql per file, do not include ";"
query = select statement
field = seperator string between fields
record= seperator string between records
file = output file name(default: uldrdata.txt)
read = set DB_FILE_MULTIBLOCK_READ_COUNT at session level
sort = set SORT_AREA_SIZE & SORT_AREA_RETAINED_SIZE at session level (UNIT:MB)
hash = set HASH_AREA_SIZE at session level (UNIT: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
mode = sqlldr option, INSERT or APPEND or REPLACE or TRUNCATE
log = log file name, prefix with + to append mode
long = maximum long field size
array = array fetch size
head = 第一行是否為字段名(head=on), 默認為off
for field and record, you can use '0x' to specify hex character code,
\r=0x0d \n=0x0a |=0x7c ,=0x2c \t=0x09
一、導出數據
d:\>ociuldr user=test/test@acf query="select * from test" file=test.txt table=test
二、查看導出內容
1,a
2,b
3,c
4,d
5,e
6,f
三、查看自動生成的控制文件
--
-- Generated by OCIULDR
--
OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 'test.txt' "STR X'0a'"
INTO TABLE test
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS
(
ID CHAR(40),
NAME CHAR(10)
)
四、可以嘗試使用這個控制文件將數據加載到數據庫中
d:\>sqlldr test/test@acf control=test_sqlldr.ctl
這樣數據就加載到數據庫中。對於大數據庫表的導出ociuldr工具還支持按照不同的批量導出數據，這通過一個參數batch來實現，默認一個batch是50萬條記錄，如果不指定batch為2就表示100萬條記錄換一個文件，默認這個選項值是0，就是指不生成多個文件。
在指定batch選項後，需要指定file選項來定義生成的文件名，文件名中間需要包含“%d”字樣，在生成文件時，“%d”會打印成序號，請看以下一個測試：
D:\>ociuldr user=test/test@acf query="select * from test" batch=1 file=test_%d.txt table=test
剛才測試了一下,果然是強悍, 用spool按要求導出10萬條記錄要好幾分鐘, 用ociuldr導出來用了一秒,或許一秒都不到, NB!
