Oracle spool导出数据

freezingsun 2月 7, 2014 Oracle spool导出数据已关闭评论

在工作中，有时需要将表中的数据查询出来，保存成txt格式或者csv格式，以便于做数据检索与分析。

在超过几万行或者几十万行数据，乃至上百万行数据时，我们使用PLSQL在查询所有记录时会报临时空间不足的错误。

这时我们需要使用spool命令将数据导出成txt或者csv格式。

为了保证数据格式的完整性，我们需要在使用spool前加入一些参数。

spool常用的设置

set colsep' '; //域输出分隔符

set echo off; //显示start启动的脚本中的每个sql命令，缺省为on

set feedback off; //回显本次sql命令处理的记录条数，缺省为on

set heading off; //输出域标题，缺省为on

set pagesize 0; //输出每页行数，缺省为24,为了避免分页，可设定为0。

set termout off; //显示脚本中的命令的执行结果，缺省为on

set trimout on; //去除标准输出每行的拖尾空格，缺省为off

set trimspool on; //去除重定向(spool)输出每行的拖尾空格，缺省为off

通常情况下，我们使用SPOOL方法，将数据库中的表导出为文本文件的时候会采用两种方法，如下述：

方法一：采用以下格式脚本

set colsep '|' ------设置列分隔符

set trimspool on

set linesize 20000

set pagesize 0

set newpage 1

set heading off

set term off

spool 路径+文件名

select * from tablename;

spool off

方法二：采用以下脚本

set trimspool on

set linesize 20000

set pagesize 0

set newpage 1

set heading off

set term off

spool 路径+文件名

select col1 ||'|'|| col2 ||'|'|| col3 ||'|'|| col4 from tablename;

spool off

spool的其它参数

set colsep' ';　　　　 //-域输出分隔符

set newp none //设置查询出来的数据分多少页显示，如果需要连续的数据，中间不要出现空行就把newp设置为none，这样输出的数据行都是连续的，中间没有空行之类的

set echo off;　　　　 //显示start启动的脚本中的每个sql命令，缺省为on

set echo on //设置运行命令是是否显示语句

set feedback on; //设置显示“已选择XX行”

set feedback off;　 //回显本次sql命令处理的记录条数，缺省为on即去掉最后的 "已经选择10000行"

set heading off;　　 //输出域标题，缺省为on 设置为off就去掉了select结果的字段名，只显示数据

set pagesize 0;　　 //输出每页行数，缺省为24,为了避免分页，可设定为0。

set linesize 80;　　 //输出一行字符个数，缺省为80

set numwidth 12;　 //输出number类型域长度，缺省为10

set termout off;　　 //显示脚本中的命令的执行结果，缺省为on

set trimout on;　　　//去除标准输出每行的拖尾空格，缺省为off

set trimspool on;　　//去除重定向(spool)输出每行的拖尾空格，缺省为off

set serveroutput on; //设置允许显示输出类似dbms_output

set timing on; //设置显示“已用时间：XXXX”

set autotrace on-; //设置允许对执行的sql进行分析

set verify off //可以关闭和打开提示确认信息old 1和new 1的显示.

上面是用脚本对数据进行导出操作的方式，使用Navicat Premium数据库客户端工具，对于大数据的导入导出就非常的方便了，不需要再编写那么多脚本，也不用考虑到导出的数据格式问题。

附Navicat Premium导数据的方法

打开Navicat Premium软件，配置Oralce数据库连接属性

选择对应的schema，并选择对应的表，进行查询

将编写好的脚本粘贴到查询编辑器中

执行导出向导，选择导出的对应格式以及保存路径

执行导出操作，完成数据导出

1、配置连接属性

2、在schemas下选择对应的用户

3、新建查询编辑器

4、编辑器界面

5、导出向导

6、导出excel格式

7、包含列标题

8、选择文件保存路径，保存至Y盘

9、执行开始操作，完成导数操作

这样导出来的数据，不必担心数据格式问题，并且效率也很高。

『—ENDOF—』