ociuldr 支持分多个数据文件

在审计工作，将几亿条的oracle数据通过sqlserver自带工具导入到sqlserver中，速度不是特别的理想，虽然通过视图方式能提高一些速度，但是既不简洁，也不方便。

用ociuldr工具，可以支持生成多个数据文件，并通过bcp方式导入到sqlserver中，速度狠理想。

ociuldr工具默认一个batch是50W条记录（即：batch=2表示100W条记录生成一个文件），通过指定file选项来定义生成的数据文件名，中间请用包含” %b “字样，
” %b “ 会被打印成序列号：

在虚拟机模拟如下：

SQL> select count(*) fromyoon;

COUNT(*)
----------
7340032

SQL> exit
Disconnected from Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bitProduction
With the Partitioning, OLAP, DataMining and Real Application Testing options

1. 将ociuldr工具上传至BIN目录下

2. chown oracle.oinstall ociuldr

3. chmod 775 ociuldr

[oracle@db01 ~]$ ociuldr -help
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
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 sessionlevel (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
buffer= sqlldr READSIZE and BINDSIZE, default 16 (MB)

for field and record, you can use '0x' to specify hex charactercode,
\r=0x0d \n=0x0a |=0x7c ,=0x2c \t=0x09[oracle@db01 ~]# su -oracle

[oracle@db01 ~]$ ociuldr user=scott/tiger@orcl query="select * from scott.yoon"field='<[!]>' record='<[end]>' file=/u01/yoon_%b.txttable=scott.yoon batch=2

2050 bytes allocated for column EMPNO (1)
550 bytes allocated for column ENAME(2)
500 bytes allocated for column JOB(3)
2050 bytes allocated for column MGR(4)
1050 bytes allocated for columnHIREDATE (5)
2050 bytes allocated for column SAL(6)
2050 bytes allocated for column COMM(7)
2050 bytes allocated for columnDEPTNO (8)

0 rows exported at 2014-10-3001:13:21
500000 rows exported at 2014-10-3001:13:46
1000000 rows exported at 2014-10-3001:14:15
output file /u01/yoon_1.txt closed at1000000 rows.
500000 rows exported at 2014-10-3001:14:40
1000000 rows exported at 2014-10-3001:15:07
output file /u01/yoon_2.txt closed at1000000 rows.
500000 rows exported at 2014-10-3001:15:36
1000000 rows exported at 2014-10-3001:16:02
output file /u01/yoon_3.txt closed at1000000 rows.
500000 rows exported at 2014-10-3001:16:29
1000000 rows exported at 2014-10-3001:16:48
output file /u01/yoon_4.txt closed at1000000 rows.
500000 rows exported at 2014-10-3001:17:05
1000000 rows exported at 2014-10-3001:17:12
output file /u01/yoon_5.txt closed at1000000 rows.
500000 rows exported at 2014-10-3001:17:17
1000000 rows exported at 2014-10-3001:17:22
output file /u01/yoon_6.txt closed at1000000 rows.
500000 rows exported at 2014-10-3001:17:27
1000000 rows exported at 2014-10-3001:17:32
output file /u01/yoon_7.txt closed at1000000 rows.
340032 rows exported at 2014-10-3001:17:38
output file /u01/yoon_8.txt closed at340032 rows.

[root@db01 u01]# ls
app backup yoon_1.txt yoon_2.txtyoon_3.txt yoon_4.txt yoon_5.txt yoon_6.txt yoon_7.txtyoon_8.txt