Oracle文本数据导出方法总结

简介: 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

目录
相关文章
|
6月前
|
SQL 运维 Oracle
【迁移秘籍揭晓】ADB如何助你一臂之力,轻松玩转Oracle至ADB的数据大转移?
【8月更文挑战第27天】ADB(Autonomous Database)是由甲骨文公司推出的自动化的数据库服务,它极大简化了数据库的运维工作。在从传统Oracle数据库升级至ADB的过程中,数据迁移至关重要。
97 0
|
6月前
|
数据采集 Oracle 关系型数据库
实时计算 Flink版产品使用问题之怎么实现从Oracle数据库读取多个表并将数据写入到Iceberg表
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
3月前
|
存储 Oracle 关系型数据库
【赵渝强老师】Oracle的还原数据
Oracle数据库中的还原数据(也称为undo数据或撤销数据)存储在还原表空间中,主要用于支持查询的一致性读取、实现闪回技术和恢复失败的事务。文章通过示例详细介绍了还原数据的工作原理和应用场景。
【赵渝强老师】Oracle的还原数据
|
3月前
|
SQL Oracle 关系型数据库
Oracle数据库优化方法
【10月更文挑战第25天】Oracle数据库优化方法
67 7
|
3月前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的联机重做日志文件与数据写入过程
在Oracle数据库中,联机重做日志文件记录了数据库的变化,用于实例恢复。每个数据库有多组联机重做日志,每组建议至少有两个成员。通过SQL语句可查看日志文件信息。视频讲解和示意图进一步解释了这一过程。
|
3月前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的数据文件
在Oracle数据库中,数据库由多个表空间组成,每个表空间包含多个数据文件。数据文件存储实际的数据库数据。查询时,如果内存中没有所需数据,Oracle会从数据文件中读取并加载到内存。可通过SQL语句查看和管理数据文件。附有视频讲解及示例。
|
5月前
|
Oracle 安全 关系型数据库
Oracle数据恢复—Oracle数据库误删除的数据恢复方法探讨
删除Oracle数据库数据一般有以下2种方式:delete、drop或truncate。下面针对这2种删除oracle数据库数据的方式探讨一下oracle数据库数据恢复方法(不考虑全库备份和利用归档日志)。
|
4月前
|
Oracle 关系型数据库 数据库
oracle数据创建同义词
oracle数据创建同义词
69 0
|
6月前
|
SQL 监控 Oracle
Oracle数据误删不用怕,跟我来学日志挖掘
Oracle数据误删不用怕,跟我来学日志挖掘
120 0
|
6月前
|
SQL Oracle 关系型数据库
Oracle误删数据怎么恢复?
Oracle误删数据怎么恢复?
73 0

推荐镜像

更多