Oracle文本数据（csv、txt）导出方法总结
前 言
在日常维护工作中，经常会遇到客户要求将标的数据库导出为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| 1 2 3 4 5 6 7 8 9 10 11 12 13 | 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| 1 2 3 | 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文件。
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]| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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}]| 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| 1 2 3 4 5 6 | 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.| 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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | 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 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 | [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
相关文章
- Oracle文本导出工具之sqluldr2
- MySQL之Navicat、into outfile、mysql命令、mysqldump、mysqlpump、mydumper等导出导入文本数据（csv、txt、sql等）
- Oracle sqlplus参数autocommit（自动提交）的设置
- SQLPlus命令使用手册
- Oracle11g版本对 SQLPlus错误日志的记录功能--errorlogging参数
- 【DG】怎么从Oracle备库导出数据
- MySQL Load Data文本导入详解
- 使用数据泵基于flashback_scn+OGG微服务零停机迁移12c到19c
- 使用XTTS异机迁移并升级11.2.0.4到12.2.0.1
- Different Upgrade Methods For Upgrading Your Database (Doc ID 419550.1)
- 使用rman备份恢复迁移Oracle 11.2.0.4到新机器并使用AutoUpgrade自动升级到12.2.0.1版本
- 手工迁移数据库Oracle 11.2.0.4到新机器并升级到12.2.0.1版本
- Oracle数据泵的兼容性 Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions (Doc ID 553337.1)
- 数据泵导入报错UDI-03113、UDI-03114和ORA-03113、ORA-03114、ORA-04036参数pga_aggregate_limit太小
- impdp数据泵导入使用table_exists_action=SKIP存在的问题以及如何接着导入数据而不用重新导入
- [INS-32104] Specified Oracle Home user is not the owner of the specified Oracle Base
- 常见问题：在 Windows 平台的 Oracle 12.1 数据库版本上的 Oracle Home 用户 (Doc ID 2101982.1、Doc ID 1529702.1)
- 启动Oracle数据库报错ORA-48173、ORA-48187、OSD-00002、O/S-Error: (OS 5) Access is denied
- 在Oracle中，什么是Quote(q) Q语法？
- 在Oracle中行转列函数PRINT_TABLE的用法
目录 返回
首页