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  =
       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 query="select * from scott.yoon" field='' record='' 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