Oracle海量数据导出效率翻倍-sqluldr2从安装到实战全攻略

原创飞天 2025-07-27

1157

sqluldr2说明

sqluldr2（全称 SQL* UnLoader）是由中国数据库领域专家楼方鑫使用C语言开发的一款高性能Oracle数据导出工具，能将数据导出到txt/csv等格式，sqluldr2凭借OCI接口和并行处理技术，在导出海量数据时显著优于传统方法（如SQL*Plus的spool或PL/SQL Developer）。可以在windows、linux平台使用，请针对不同的平台下载不同的版本，这个工具也还有MySQL数据库（Linux / MacOS）的版本。sqluldr2工具非商业用途免费，商业场景需支付 100美元/服务器的许可费用。

sqluldr2命令用法

[oracle@node3:/home/oracle]$ sqluldr2

SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1
(@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.

License: Free for non-commercial useage, else 100 USD per server.

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

Valid Keywords:
   user    = username/password@tnsname
   sql     = SQL file name
   query   = select statement
   field   = separator string between fields
   record  = separator string between records
   rows    = print progress for every given rows (default, 1000000) 
   file    = output file name(default: uldrdata.txt)
   log     = log file name, prefix with + to append mode
   fast    = auto tuning the session level parameters(YES)
   text    = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
   charset = character set name of the target database.
   ncharset= national character set name of the target database.
   parfile = read command option from parameter file 

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

sqluldr2下载使用

1、下载并解压zip包：

unzip sqluldr2_linux64_10204.zip

解压后，会生成sqluldr2_linux64_10204.bin文件。
2、为了方便后续使用，把sqluldr2_linux64_10204.bin文件重命名，并且放到$ORACLE_HOME/bin目录下。如下：

mv sqluldr2_linux64_10204.bin  sqluldr2
cp sqluldr2 $ORACLE_HOME/bin
chmod 755 $ORACLE_HOME/bin/sqluldr2

sqluldr2使用场景

场景1、导出单表的全部数据（query=表名）

sqluldr2 user=scott/tiger query=emp file=emp.txt
默认会同时生成ctl控制文件。

执行过程如下：

[oracle@node3:/home/oracle]$ sqluldr2 user=scott/tiger query=emp file=emp.txt
           0 rows exported at 2025-07-27 10:41:56, size 0 MB.
          14 rows exported at 2025-07-27 10:41:56, size 0 MB.
         output file emp.txt closed at 14 rows, size 0 MB.
[oracle@node3:/home/oracle]$ cat 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
默认还会生成ctl控制文件，如下：
[oracle@node3:/home/oracle]$ cat emp_sqlldr.ctl
--
-- SQL*UnLoader: Fast Oracle Text Unloader (GZIP), Release 3.0.1
-- (@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.
--
--  CREATE TABLE emp (
--    EMPNO NUMBER(4),
--    ENAME VARCHAR2(10),
--    JOB VARCHAR2(9),
--    MGR NUMBER(4),
--    HIREDATE DATE,
--    SAL NUMBER(7,2),
--    COMM NUMBER(7,2),
--    DEPTNO NUMBER(2)
--  );
--
OPTIONS(BINDSIZE=2097152,READSIZE=2097152,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 'emp.txt' "STR X'0a'"
INSERT INTO TABLE emp
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS 
(
  "EMPNO" CHAR(6) NULLIF "EMPNO"=BLANKS,
  "ENAME" CHAR(10) NULLIF "ENAME"=BLANKS,
  "JOB" CHAR(9) NULLIF "JOB"=BLANKS,
  "MGR" CHAR(6) NULLIF "MGR"=BLANKS,
  "HIREDATE" DATE "YYYY-MM-DD HH24:MI:SS" NULLIF "HIREDATE"=BLANKS,
  "SAL" CHAR(10) NULLIF "SAL"=BLANKS,
  "COMM" CHAR(10) NULLIF "COMM"=BLANKS,
  "DEPTNO" CHAR(4) NULLIF "DEPTNO"=BLANKS
)

场景2、导出单表的部分数据（query=select * from 表名 where…）

sqluldr2 user=scott/tiger query="select * from emp where empno=7499" file=emp7499.txt

执行过程如下：

[oracle@node3:/home/oracle]$ sqluldr2 user=scott/tiger query="select * from emp where empno=7499" file=emp7499.txt
           0 rows exported at 2025-07-27 10:45:00, size 0 MB.
           1 rows exported at 2025-07-27 10:45:00, size 0 MB.
         output file emp7499.txt closed at 1 rows, size 0 MB.
[oracle@node3:/home/oracle]$ cat emp7499.txt 
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
[oracle@node3:/home/oracle]$

场景3、导出多表关联的数据（query=select … from a,b… where …）

sqluldr2 user=scott/tiger query="select dept.deptno,emp.empno,emp.sal from dept,emp where dept.deptno=emp.deptno and dept.deptno=20" file=dept20.txt

执行过程如下：

[oracle@node3:/home/oracle]$ sqluldr2 user=scott/tiger query="select dept.deptno,emp.empno,emp.sal from dept,emp where dept.deptno=emp.deptno and dept.deptno=20" file=dept20.txt
           0 rows exported at 2025-07-27 10:48:29, size 0 MB.
           5 rows exported at 2025-07-27 10:48:29, size 0 MB.
         output file dept20.txt closed at 5 rows, size 0 MB.
[oracle@node3:/home/oracle]$ cat dept20.txt 
20,7369,800
20,7566,2975
20,7788,3000
20,7876,1100
20,7902,3000

场景4、指定字段分隔符、记录分隔符、字符集（field= record= charset=）

sqluldr2 user=scott/tiger query="select dept.deptno,emp.empno,emp.sal from dept,emp where dept.deptno=emp.deptno and dept.deptno=20"  file=dept20field.txt field='#' record='0x0a' charset=ZHS16GBK

执行过程如下：

[oracle@node3:/home/oracle]$ sqluldr2 user=scott/tiger query="select dept.deptno,emp.empno,emp.sal from dept,emp where dept.deptno=emp.deptno and dept.deptno=20"  file=dept20field.txt field='#' record='0x0a' charset=ZHS16GBK
           0 rows exported at 2025-07-27 10:59:05, size 0 MB.
           5 rows exported at 2025-07-27 10:59:05, size 0 MB.
         output file dept20field.txt closed at 5 rows, size 0 MB.
[oracle@node3:/home/oracle]$ cat dept20field.txt
20#7369#800
20#7566#2975
20#7788#3000
20#7876#1100
20#7902#3000

场景5、输出表头（head=yes）

sqluldr2 user=scott/tiger query="select dept.deptno,emp.empno,emp.sal from dept,emp where dept.deptno=emp.deptno and dept.deptno=20"  head=yes file=dept20head.txt

执行过程如下：

[oracle@node3:/home/oracle]$ sqluldr2 user=scott/tiger query="select dept.deptno,emp.empno,emp.sal from dept,emp where dept.deptno=emp.deptno and dept.deptno=20"  head=yes file=dept20head.txt
           0 rows exported at 2025-07-27 16:36:26, size 0 MB.
           5 rows exported at 2025-07-27 16:36:26, size 0 MB.
         output file dept20head.txt closed at 5 rows, size 0 MB.
[oracle@node3:/home/oracle]$ cat dept20head.txt 
DEPTNO,EMPNO,SAL
20,7369,800
20,7566,2975
20,7788,3000
20,7876,1100
20,7902,3000

场景6、数据按记录数切片（rows=行数 batch=yes）

SQL> SELECT count(*) from emp_t;

  COUNT(*)
----------
     57344

sqluldr2 user=scott/tiger query="select * from emp_t"   file=emp_t_rows%B.txt rows=10000 batch=yes

执行过程如下：

[oracle@node3:/home/oracle]$  sqluldr2 user=scott/tiger query="select * from emp_t"   file=emp_t_rows%B.txt rows=10000 batch=yes
           0 rows exported at 2025-07-27 16:46:32, size 0 MB.
       10000 rows exported at 2025-07-27 16:46:33, size 0 MB.
         output file emp_t_rows1.txt closed at 10000 rows, size 1 MB.
       10000 rows exported at 2025-07-27 16:46:33, size 1 MB.
         output file emp_t_rows2.txt closed at 10000 rows, size 1 MB.
       10000 rows exported at 2025-07-27 16:46:33, size 1 MB.
         output file emp_t_rows3.txt closed at 10000 rows, size 2 MB.
       10000 rows exported at 2025-07-27 16:46:33, size 2 MB.
         output file emp_t_rows4.txt closed at 10000 rows, size 2 MB.
       10000 rows exported at 2025-07-27 16:46:33, size 2 MB.
         output file emp_t_rows5.txt closed at 10000 rows, size 3 MB.
        7344 rows exported at 2025-07-27 16:46:33, size 3 MB.
         output file emp_t_rows6.txt closed at 7344 rows, size 3 MB.
[oracle@node3:/home/oracle]$ ll emp_t*
-rw-r--r-- 1 oracle oinstall 532140 Jul 27 16:46 emp_t_rows1.txt
-rw-r--r-- 1 oracle oinstall 532139 Jul 27 16:46 emp_t_rows2.txt
-rw-r--r-- 1 oracle oinstall 532139 Jul 27 16:46 emp_t_rows3.txt
-rw-r--r-- 1 oracle oinstall 532156 Jul 27 16:46 emp_t_rows4.txt
-rw-r--r-- 1 oracle oinstall 532110 Jul 27 16:46 emp_t_rows5.txt
-rw-r--r-- 1 oracle oinstall 390836 Jul 27 16:46 emp_t_rows6.txt
-rw-r--r-- 1 oracle oinstall 390836 Jul 27 16:45 emp_t_rows.txt
[oracle@node3:/home/oracle]$ wc -l emp_t_rows1.txt
10000 emp_t_rows1.txt
[oracle@node3:/home/oracle]$

场景7、数据按文件大小切片（size=大小 batch=yes）

多次插入记录后，表中记录数：
SQL> select count(*) from emp_t;

  COUNT(*)
----------
    229376

sqluldr2 user=scott/tiger query="select * from emp_t"   file=emp_t_size%B.txt size=5MB batch=yes

执行过程如下：

[oracle@node3:/home/oracle]$ sqluldr2 user=scott/tiger query="select * from emp_t"   file=emp_t_size%B.txt size=5MB batch=yes
           0 rows exported at 2025-07-27 16:53:39, size 0 MB.
         output file emp_t_size1.txt closed at 157637 rows, size 8 MB.
       71739 rows exported at 2025-07-27 16:53:40, size 12 MB.
         output file emp_t_size2.txt closed at 71739 rows, size 12 MB.
[oracle@node3:/home/oracle]$ ll emp_t_size*
-rw-r--r-- 1 oracle oinstall 8388478 Jul 27 16:53 emp_t_size1.txt
-rw-r--r-- 1 oracle oinstall 3817602 Jul 27 16:53 emp_t_size2.txt
[oracle@node3:/home/oracle]$ du -sh emp_t_size*
8.0M    emp_t_size1.txt
3.7M    emp_t_size2.txt
[oracle@node3:/home/oracle]$

场景8、输出日志（log=）

sqluldr2 user=scott/tiger query=emp file=emp.txt log=+emp.log
注意：log=+emp.log，其中+代表append模式。

执行过程如下：

[oracle@node3:/home/oracle]$ sqluldr2 user=scott/tiger query=emp file=emp.txt log=+emp.log
[oracle@node3:/home/oracle]$ cat emp.log
           0 rows exported at 2025-07-27 16:55:42, size 0 MB.
          14 rows exported at 2025-07-27 16:55:42, size 0 MB.
         output file emp.txt closed at 14 rows, size 0 MB.

关于作者：
网名：飞天，墨天轮2024年度优秀原创作者，拥有 Oracle 10g OCM 认证、PGCE认证、MySQL 8.0 OCP认证以及OBCA、KCP、KCSM、ACP、磐维等众多国产数据库认证证书，目前从事Oracle、Mysql、PostgresSQL、磐维数据库管理运维工作，喜欢结交更多志同道合的朋友，热衷于研究、分享数据库技术。
微信公众号：飞天online
墨天轮：https://www.modb.pro/u/15197
如有任何疑问，欢迎大家留言，共同探讨~~~