Oracle各版本文本数据生成方案

来源: https://www.ucloud.cn/yun/129126.html 作者: IT那活儿 发布日期: 发布于2023-01-11 13:19

Oracle各版本文本数据生成方案

点击上方“IT那活儿**”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！**

一

前 言

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

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

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

二

解决方案

2.1 oracle版本为11g

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

sqlplus -S username/password<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.csvset timming on;SELECT "||machine||","||program||","||port||","||process||","||username||" from dbauser.session_01;spool offeof

除此之外也可以使用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文件。

2.2 oracle版本为12.2以上

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

标准语法为：

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

官方文档说明如下：

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

生成CSV文件格式：

指定特殊分隔符：

本文作者：袁 钢(上海新炬中北团队）

本文来源：“IT那活儿”公众号