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

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

1 2	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

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]

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]

标准语法为：

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

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

1	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

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.

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;

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 ~]$

[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

打赏赞(2)

标签： csv文件 Oracle spool sqlplus sqluldr2 txt文件数据迁移文本导出文本文件

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

2023年 5月
一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

AI (2)
ClickHouse (5)
DM达梦 (9)
Docker (34)
GBase (34)
Greenplum (85)
HANA (2)
K8S (14)
Kafka (3)
Memcached (2)
MongoDB (3)
MySQL (177)
NoSQL (3)
OceanBase (14)
OGG (43)
openGauss (9)
Oracle (590)
OS (329)
PostgreSQL (229)
Python (26)
Redis (18)
SQL Server (106)
SQLite (1)
StarRocks (9)
TDSQL (5)
TiDB (18)
YMatrix (5)
中间件 (9)
云 (10)
人大金仓KingBase (7)
其它 (78)
培训考证 (43)
大数据 (4)
存储 (4)
巡检监控 (43)
工具 (65)
数据迁移 (16)
架构篇 (9)
生活 (8)
电脑 (14)
网络 (78)
育儿 (5)
视频 (17)
面试笔试 (38)

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

小麦苗

Oracle文本数据（csv、txt）导出方法总结

前言

导出方案

spool方式（通用）

sqluldr2

oracle版本为12.2以上

总结

参考

相关文章

您可能还喜欢...

发表回复取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

Oracle文本数据（csv、txt）导出方法总结

前 言

导出方案

spool方式（通用）

sqluldr2

oracle版本为12.2以上

总结

参考

相关文章

您可能还喜欢...

Oracle异常恢复BBED系列

DG参数 LOG_ARCHIVE_DEST_n

Oracle创建控制文件时报错ORA-15173

发表回复 取消回复

博客寄语

博客概况

本站其它工具

搜标题

搜索本网站

标签云☁

登陆本博客

博客日历

博客归档

博客分类

前言

发表回复取消回复