ociuldr 支持分多个数据文件

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

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

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

在虚拟机模拟如下：

SQL> select count(*) from yoon;

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

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining 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 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
buffer= sqlldr READSIZE and BINDSIZE, default 16 (MB)

for field and record, you can use '0x' to specify hex character code,
\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.txt table=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 column HIREDATE (5)
2050 bytes allocated for column SAL (6)
2050 bytes allocated for column COMM (7)
2050 bytes allocated for column DEPTNO (8)

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

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