前 言
在日常维护工作中,经常会遇到客户要求将标的数据库导出为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文件。
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、