Oracle文本数据导出方法总结-阿里云开发者社区

前言

在日常维护工作中，经常会遇到客户要求将标的数据库导出为TXT、CSV等文件。

在数据量较少的情况下PL/SQL、toad、Navicat等工具都可以满足要求，速度快而且方便。

但这些工具并不适用于数据量大的情况，在数据量大的时候工具会卡死，在不同的数据库版本，有不同的解决方案。

导出方案

spool方式（通用）

对于数据量大的情况我们一般使用SPOOL方式进行导出：

sqlplus -S username/password<<eof
set linesize 2000 pages 0;
set numwidth 30;
set heading off ;
set feedback off ;
set timing off;
set trimspool on;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
spool session.csv
set timming on;
SELECT '"'||machine||'","'||program||'","'||port||'","'||process||'","'||username||'"' from dbauser.session_01;
spool off
eof

sqluldr2

详细参考：https://www.xmmup.com/oraclewenbendaochugongjuzhisqluldr2.html

sqluldr2缺点：导出不能包含CLOB字段，否则会报错。

除此之外也可以使用sqluldr2，该工具可快速将数据导出为TXT、CSV格式，支持并行导出、多种分隔符、自动拆分文件、通配符。需要额外下载安装。

导出格式：

sqluldr2 scott/tiger query="scott.emp"  head=yes
file=emp%b.csv log= emp.log charset=UTF8 head=no batch=yes size=100

常用参数：

user = username/password@tnsname 用户名、密码；
query = select statement 选择语句（可直接写表名、查询运算语句、sql文本）；
sql = SQL file name sql语句文件(sql语句复杂时写入文本，由query调用)；
field = separator string between fields （分隔符，默认逗号分隔）；
file = output file name （导出文件名）；
log = log file name, prefix with + to append mode(日志文件)；
charset = character set name of the target database（字符集）；
size (最大输出文件大小mb,按大小拆分文件)；
rows(按输出行数拆分文件)。

示例：

导出dbauser.session_01表按100MB每个文件进行拆分。

sqluldr2 dbauser/dba_2014 query="dbauser.session_01"  
file=/dumpbak01/oracledmp/session_%b.CSV charset=UTF8
head=yes  batch=yes size=100

使用spool导出和sqluldr2导出，生成的文件大小基本一致，但sqluldr2只用30秒，spool使用了2分31秒，sqluldr2效率是spool的5倍以上。

相比spool，sqluldr2在使用上更为便捷，功能也更为全面，效率也更高，因此在11g版本建议使用sqluldr2来导出TXT、CSV文件。

oracle版本为12.2以上

在oracle版本为12.2以上时，oracle提供了新的功能导出CSV文件,在会话中设置set markup csv on即可生成CSV文件。

在oracle版本为12.2以上时，使用set markup csv on可以更为便捷的导出CSV文件。

官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/SET-system-variable-summary.html#GUID-0AA910C4-C22A-4A9E-BE13-AAA059CC7919

help如下：

SYS@lhrsdb> help

 HELP
 ----

 Accesses this command line help system. Enter HELP INDEX or ? INDEX
 for a list of topics.

 You can view SQL*Plus resources at
     http://www.oracle.com/technology/documentation/

 ******************************************************************************
 **  Top 12.2 features:                                                      **
 **    - Fast retrieval of data as CSV for use in applications like          **
 **      SQL*Loader. Use SQLPLUS -M "CSV ON" or SET MARKUP CSV ON            **
 **    - Improved defaults and optimizations for reports. Use SQLPLUS -F     **
 **    - Command recall. Use SET HISTORY ON and HISTORY to list previous     **
 **      commands.                                                           **
 ******************************************************************************

 HELP|? [topic]

标准语法为：

SET MARK(UP) CSV {ON/OFF} [DELIMI[TER] character] [QUOTE {ON/OFF}]

默认生成CSV格式，也可指定分隔符。

SET MARKUP CSV ON
SET MARKUP CSV ON  QUOTE OFF
SET MARKUP CSV ON DELIMITER |


-- sqlplus直接生成
sqlplus -S -F -M "CSV ON"   lhr/lhr@127.0.0.1/lhrsdb  @a.txt > a.csv

示例：


SYS@lhrsdb> SET MARKUP CSV ON
SYS@lhrsdb> select * from scott.emp;

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
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

14 rows selected.

SYS@lhrsdb> SET MARKUP CSV ON QUOTE OFF
SYS@lhrsdb> select * from scott.emp;

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
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

14 rows selected.


SYS@lhrsdb> SET MARKUP CSV ON DELIMITER |
SYS@lhrsdb> select * from scott.emp;

EMPNO|ENAME|JOB|MGR|HIREDATE|SAL|COMM|DEPTNO
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

14 rows selected.

sqlplus直接生成csv文件：

[oracle@lhrora19c ~]$ more a.txt 
set feedback off ;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select * from scott.emp;

exit


[oracle@lhrora19c ~]$ sqlplus -S -F -M "CSV ON"   lhr/lhr@127.0.0.1/lhrsdb  @a.txt > a.csv
[oracle@lhrora19c ~]$ more a.csv

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
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
[oracle@lhrora19c ~]$

总结

1、

参考

https://mp.weixin.qq.com/s/fy8c2N3e6mq_U4cXyLrPeA

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/generating-HTML-reports-from-SQL-Plus.html#GUID-8DA36816-96D7-4575-9FD3-F727F6205EF2

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/SET-system-variable-summary.html#GUID-0AA910C4-C22A-4A9E-BE13-AAA059CC7919

Oracle文本数据导出方法总结

前言

导出方案

spool方式（通用）

sqluldr2

oracle版本为12.2以上

总结

参考

热门文章

最新文章

相关电子书

相关实验场景

推荐镜像

Oracle文本数据导出方法总结

前 言

导出方案

spool方式（通用）

sqluldr2

oracle版本为12.2以上

总结

参考

热门文章

最新文章

相关电子书

相关实验场景

推荐镜像

前言