Sqluldr2工具的使用

下载地址 http://www.anysql.net/download

[oracle@ocm1 ~]$ pwd
/home/oracle
[oracle@ocm1 ~]$ ls /home/oracle/sqluldr2_linux32_10204.bin 
/home/oracle/sqluldr2_linux32_10204.bin
[oracle@ocm1 ~]$ vi .bash_profile  --添加环境变量
[oracle@ocm1 ~]$ cat .bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/bin:$ORACLE_HOME/lib:/lib:/usr/lib
export PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin

不设置环境变量会报如下错误

[oracle@ocm1 ~]$ ./sqluldr2_linux32_10204.bin 
./sqluldr2_linux32_10204.bin: error while loading shared libraries: libclntsh.so.10.1: cannot open shared object file: No such file or directory

查看使用说明

[oracle@ocm1 ~]$ source .bash_profile 
etc_bashrc
[oracle@ocm1 ~]$ ./sqluldr2_linux32_10204.bin 

SQL*UnLoader: Fast Oracle Text Unloader (GZIP), Release 3.0.0
(@) Copyright Lou Fangxin 2004/2009, all rights reserved.

Usage: SQLULDR2 keyword=value [,keyword=value,...]

Valid Keywords:
   user    = username/password@tnsname
   sql     = SQL file name
   query   = select statement
   field   = seperator string between fields
   record  = seperator string between records
   head    = print row header(Yes|No)
   file    = output file name(default: uldrdata.txt)
   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) 
   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)
   width   = customized max column width (w1:w2:...) 
   quote   = optional quote string 
   rows    = print progress for every given rows (default, 1000000) 
   data    = disable real data unload (NO, OFF) 
   parfile = read command option from parameter file 
   alter   = alter session SQLs to be execute before unload 
   safe    = use large buffer to avoid ORA-24345 error (Yes|No) 
   crypt   = use encrypted conneciton string (Yes|No) 

  for field and record, you can use '0x' to specify hex character code,
  \r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23

尝试导出小表查看结果

[oracle@ocm1 ~]$ ./sqluldr2_linux32_10204.bin user=scott/tiger@prod1 query="select * from emp" file="/tmp/emp.txt";
       0 rows exported at 2016-11-25 21:46:16, size 0 MB.
      14 rows exported at 2016-11-25 21:46:16, size 0 MB.
         output file /tmp/emp.txt closed at 14 rows, size 0 MB.
[oracle@ocm1 ~]$ cat /tmp/emp.txt 
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10 

再导出个1000W行的表，速度较快

[oracle@ocm1 ~]$ ./sqluldr2_linux32_10204.bin user=eoda/foo@prod1 query="select * from big_table" file="/tmp/bt.txt";
       0 rows exported at 2016-11-25 21:47:53, size 0 MB.
 1000000 rows exported at 2016-11-25 21:48:04, size 120 MB.
 2000000 rows exported at 2016-11-25 21:48:16, size 244 MB.
 3000000 rows exported at 2016-11-25 21:48:28, size 368 MB.
 4000000 rows exported at 2016-11-25 21:48:40, size 496 MB.
 5000000 rows exported at 2016-11-25 21:48:53, size 620 MB.
 6000000 rows exported at 2016-11-25 21:49:09, size 744 MB.
 7000000 rows exported at 2016-11-25 21:49:23, size 868 MB.
 8000000 rows exported at 2016-11-25 21:49:39, size 992 MB.
 9000000 rows exported at 2016-11-25 21:49:50, size 1116 MB.
10000000 rows exported at 2016-11-25 21:50:03, size 1240 MB.
         output file /tmp/bt.txt closed at 10000000 rows, size 1242 MB.