[1]

http://www.google.com.hk/search?q=sqlplus+%E5%AF%BC%E5%87%BA%E8%A1%A8%E6%95%B0%E6%8D%AE&hl=en&safe=active&prmd=ivns&ei=qhmpTbKQLYTevwOtwrybCg&start=40&sa=N

oracle 导出数据到文本

最近项目中有一个数据转移的模块，需要将oracle数据库表中的数据导出到文本文件，查了一些资料，整理一下。

因为只需要导出表中的数据，所以选择了spool命令，将执行select语句时缓冲池中的数据导出到文本。

简单执行如下：

进入sqlplus，

--要导出数据存放的文本文件的地址

spool c:/export.txt

--要导出数据的sql语句

select * from tablename where ...;

spool off;

上面只是简单的导出表中内容。



导出前一半需要对导出的文本格式进行设置，因此通过执行sql脚本的方式导出表中数据

1、建立import.sql文件

内容如下：

whenever sqlerror exit sql.sqlcode rollback
--某一行出现错误，终止所有，并回滚
set echo off;
set heading off;
set colsep ' ';
--字段分隔符
set feedback off;
--不显示本次sql命令处理的记录条数，缺省为on
set pagesize 0;
--输出每页行数，缺省为24,为了避免分页，可设定为0
set linesize 5000;
--输出一行字符个数，缺省为80
set numwidth 20;
--输出number类型域长度，缺省为10
set termout off;
--显示脚本中的命令的执行结果，缺省为on
set trimout on;
--去除标准输出每行的拖尾空格，缺省为off
set trimspool on;
--是否显示替代变量被替代前后的语句
set verify off;
spool &1 append;

--&1为参数，导出文件的地址，执行sql脚本时从外边传进来，也可直接将地址写在此处

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

--字段连接，可以将一条记录放在同一行，否则导出的一条记录可能会占用文本文件中的几行
spool off;
exit

2、在sqlplus中执行 @export.sql文件地址 "导出后文本文件存放地址"

或在cmd中执行 sqlplus user/passwork@dbserver @export.sql文件地址 "导出后文本文件存放地址"

注意：执行sql脚本传参时，需要控制参数的长度，参数太长可能会报string beginning "" autoID|..." is too long. maximum size is 239 character 的错误。

用SQLPLUS脚本快速导出大量数据的脚本[日期：2008-10-09] 作者：佚名 [字体：大 中 小]

新闻简介： 那么你需要注意以下几点：
一行数据太长也不要换行；
不要在导出文件中出现SQL语句；
不要在窗口中显示查询结果，直接将结果保存到导出文件中，以提高执行速度；
不要在导出文件中出现列的标题行；
不要在导出文件中出现插入分页行；
去掉行尾空格；
不要在导出文件的最后显示：查询得到多少行。
方法：
一定要将以下内容保存为sql脚本，然后用@xx.sql去调用该脚本。否则set term off无效，查询结果将被显示在窗口中，大大降低执行速度。
而且要用column命令设置太宽的列的格式。
set lines
关 键 词:
那么你需要注意以下几点：
一行数据太长也不要换行；
不要在导出文件中出现SQL语句；
不要在窗口中显示查询结果，直接将结果保存到导出文件中，以提高执行速度；
不要在导出文件中出现列的标题行；
不要在导出文件中出现插入分页行；
去掉行尾空格；
不要在导出文件的最后显示：查询得到多少行。

方法：
一定要将以下内容保存为sql脚本，然后用@xx.sql去调用该脚本。否则set term off无效，查询结果将被显示在窗口中，大大降低执行速度。
而且要用column命令设置太宽的列的格式。

set linesize 1000
set echo off
set term off
set heading off
set pagesize 0
set trimspool on
set feedback off

col COL1 FORMAT A20
col COL_VERYLONG FORMAT A400

spool ...
select ... from ... where ... order by ...;
spool off

Oracle通过sqlplus spool导出数据
2010-09-05 20:57Oracle通过sqlplus spool导出数据
由于项目需要，需要Oracle能够将表的数据格式化到txt文本文件中，然后可以使用其它数据库db2的import，mssql的bcp导入到各自数据库中。
spool 本来是很好用的，但是在格式化数据上 ，不太熟悉set命令，于是网上找了下，在此备注下：
SQL>set colsep' '; //-域输出分隔符
SQL>set newp none //设置查询出来的数据分多少页显示，如果需要连续的数据，中间不要出现空行就把newp设置为none，这样输出的数据行都是连续的，中间没有空行之类的
SQL>set echo off; //显示start启动的脚本中的每个sql命令，缺省为on
SQL> set echo on //设置运行命令是是否显示语句
SQL> set feedback on; //设置显示“已选择XX行”
SQL>set feedback off; //回显本次sql命令处理的记录条数，缺省为on即去掉最后的 "已经选择10000行"
SQL>set heading off; //输出域标题，缺省为on 设置为off就去掉了select结果的字段名，只显示数据
SQL>set pagesize 0; //输出每页行数，缺省为24,为了避免分页，可设定为0。
SQL>set linesize 80; //输出一行字符个数，缺省为80
SQL>set numwidth 12; //输出number类型域长度，缺省为10
SQL>set termout off; //显示脚本中的命令的执行结果，缺省为on
SQL>set trimout on; //去除标准输出每行的拖尾空格，缺省为off
SQL>set trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为off
SQL>set serveroutput on; //设置允许显示输出类似dbms_output
SQL> set timing on; //设置显示“已用时间：XXXX”
SQL> set autotrace on-; //设置允许对执行的sql进行分析
set verify off //可以关闭和打开提示确认信息old 1和new 1的显示.
Oracle 导出数据到文件:
sqlplus /nolog @d:\sp.sql
d:\sp.sql内容如下:
conn system/bizcent@sgtest
set head off
set headsep off
set newp none
set linesize 100
set pagesize 10000
set sqlblanklines OFF
set trimspool ON
set termout off
set feedback off
spool d:\export.txt
select NODECODE||'&'||NODETYPE||'&'||NODENAME||'&' from "000".tbnode;
spool off
exit
这是我使用的满足了要求。

EXP、IMP、SQLLOADER、EXPDP、IMPDP、DBMS_METADATA、SQLPLUS等方面


09年马上要结束了，BLOG每天更新又坚持了一年，目前文章总数1700多篇，其中纯技术文章将近1300篇。

我比较喜欢的一句话：十年磨一剑。以前提到过，坚持10年可能会比较困难，那么至少要坚持5年，到目前为止，BLOG更新已经4年了，明年就会初步实现目标了。

重新整理一下BLOG上面文章的索引，把09年的文章放进去了，且重新整理了一下分类。

这一篇索引EXP/IMP/SQLLOADER、EXPDP/IMPDP/DBMS_METADATA、SQLPLUS等方面的文章。



EXP/IMP和SQLLOADER：

物化视图导出导入可能导致物化视图日志的失效：一个很恐怖的bug，会在意想不到的情况下造成物化视图复制环境被破坏。

JOB和导入导出：EXP/IMP时可能丢失JOB的问题。

SQLLDR装载换行符的小问题：利用SQLLOADER装载换行符的方法。

导入报错OCI-21500 [kopuigpfx1]：9204导入XMLTYPE类型的BUG。

减少全库导入时发生的错误（一）：问题描述。

减少全库导入时发生的错误（二）：通过删除已经存在对象的方式进一步减少错误的产生。

减少全库导入时发生的错误（三）：导入时增加TOID_NOVALIDATE参数，跳过对象的验证。

减少全库导入时发生的错误（四）：处理OLAPSYS和WKSYS用户的错误。

减少全库导入时发生的错误（五）：进一步减少错误，最终将上千个导入错误减少到了七个。

包含复制环境的数据库迁移（一）：物化视图站点通过EXP/IMP进行迁移。

包含复制环境的数据库迁移（二）：单主站点通过EXP/IMP进行迁移。

全库导入不会导入SYS和SYSTEM用户的对象权限：描述一个很容易被忽视的权限问题。

小议IMP操作参数COMMIT=Y（一）：描述COMMIT=Y设置的缺点。

小议IMP操作参数COMMIT=Y（二）：进一步讨论COMMIT=Y对性能的影响。

小议IMP操作参数COMMIT=Y（三）：COMMIT=Y对数据完整性的影响。

分步导入可能会遇到的问题：导入的时候如果采用表和索引分步导入的方式可能会出现问题。

常规路径导出比直接路径导出快的问题（一）：常规路径导出比直接路径快的问题。

常规路径导出比直接路径导出快的问题（二）：对比本地服务器二者执行时间。

常规路径导出比直接路径导出快的问题（三）：分析导致问题的原因。

EXP-3错误：9204导出10g数据库可能出现的错误。

导出小写的表：记录一下exp碰到小写表的时候如何处理。

EXP转换表空间：EXP过程碰到表空间的转换时需要注意的问题。

EXP转化表空间（二）：利用10g的改名表空间名称的方法解决表空间的转化问题。

EXP在9R2上导出时报错ORA-3113和ORA-24324：EXP的过程中出现了ORA-3113和ORA-24324错误。

EXP在9R2上导出时报错ORA-3113和ORA-24324（二）：继续分析错误情况。

获取导致导入失败的数据：从导出的dmp文件中获取错误的记录。

获取导致导入失败的数据（二）：继续修正方法继续找出异常的数据。

获取导致导入失败的数据（三）：最终获取了导致错误的记录。

获取导致导入失败的数据（四）：继续探讨导致SQLPLUS程序崩溃的原因。

获取导致导入失败的数据（五）：检验通过检查共享池的方法能否得到问题SQL。

SQLLOADER导入列中的空格：如何将文件中的空格作为列的值利用SQLLOADER加载到数据库中。

9i远端导出报错ORA-12571：通过客户端远端导出一个9201数据库的时候，中途出现ORA-12571错误。

对比数据泵与原始导入导出工具（一）：从总体上描述一下二者的相同和不同之处。

对比数据泵与原始导入导出工具（二）：描述数据泵的DIRECTORY、DUMPFILE和LOGFILE参数。

对比数据泵与原始导入导出工具（三）：描述数据泵的CONTENT参数。

对比数据泵与原始导入导出工具（四）：描述数据泵的INCLUDE参数。

对比数据泵与原始导入导出工具（五）：描述数据泵的EXCLUDE参数。

EXP导出外部表报错（一）：利用EXP导出外部表报错。

EXP导出外部表报错（二）：继续寻找导致错误的原因。

EXP导出外部表报错（三）：测试其他版本是否存在问题。

包含触发器的LOB表执行IMP导致EMPTY_LOB变为空：包含触发器的表，通过IMP导入原本的EMPTY_LOB将被转化为NULL。





EXPDP/IMPDP和DBMS_METADATA：

10g数据泵和导入导出性能对比（一）：比较EXP常规模式、EXP直接路径模式和EXPDP三种方式导出的区别。

10g数据泵和导入导出性能对比（二）：比较IMP和IMPDP导入的区别。

10g数据泵和导入导出性能对比（三）：提高IMPDP导入效率的方法。

10g数据泵和导入导出性能对比（四）：讨论并行度对数据泵导出的影响。

10g数据泵和导入导出性能对比（五）：讨论影响数据泵导入性能的最大因素。

10g数据泵和导入导出性能对比（六）：同时使用直接路径和并行来加速数据泵导入。

数据泵小bug：数据泵导入结束时间错误。

数据泵无法导入JOB：10g的数据泵在进行IMPDP的时候无法导入JOB。

数据泵处理隐藏列的bug：Oracle10g的数据泵在处理在线重定义生成的隐藏列时存在bug。

数据泵导入分区表统计信息报错（一）：在进行数据泵导入操作时，发现一个bug。

数据泵导入分区表统计信息报错（二）：继续深入研究这个问题。

数据泵导入分区表统计信息报错（三）：检查问题和表的MONITORING属性之间的关系。

数据泵导入分区表统计信息报错（四）：文章描述问题的解决过程。

数据泵导入分区表统计信息报错（五）：描述导致问题的原因。

数据泵导入分区表统计信息报错（六）：描述问题重现的过程。

数据泵导出时报错ORA-1422错误：DUAL表记录超过一条时，导致的错误。

Oracle10g新特性——利用外部表卸载数据：Oracle的外部表也可以用来卸载数据了。

数据泵报错ORA-39149：利用NETWORK_LINK方式导出的时候，出现了这个错误。

数据泵交换模式输入确认无效：数据泵的交互模式下，停止数据泵命令时，输入确认的命令无效。

Oracle11g新特性——数据泵（一）：介绍新增的选项DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS。

Oracle11g新特性——数据泵（二）：介绍新增的参数REMAP_DATA。

Oracle11g新特性——数据泵（三）：介绍数据泵压缩功能的提升。

Oracle11g新特性——数据泵（四）：介绍数据泵导入的时候对分区的处理。

Oracle11g新特性——数据泵（五）：介绍数据泵REUSE_DUMPFILES功能。

Oracle11g新特性——数据泵（六）：介绍数据泵REMAP_TABLE功能。

Oracle11g新特性——数据泵（七）：介绍数据泵导出的TRANSPORTABLE功能。

Oracle11g新特性——数据泵（八）：介绍数据泵导入的TRANSPORTABLE功能。

Oracle11g新特性——数据泵（九）：介绍数据泵的加密功能。

数据泵导入SKIP_CONSTRAINT_ERRORS的bug：利用这个参数可以在导入数据的时候，忽略掉违反约束的数据，而使得其他正常的数据顺利的导入。

数据泵导入SKIP_CONSTRAINT_ERRORS的bug（二）：检查其他类型的约束的情况。

数据泵导入SKIP_CONSTRAINT_ERRORS的bug（三）：通过讨论外键约束的情况将问题进一步深入。

EXPDP转化表空间：介绍10g的数据泵是如何进行表空间的转换的。

数据泵导入到不存在的用户（一）：10g版本中创建的用户必须改变用户的密码，否则无法登陆。

数据泵导入到不存在的用户（二）：描述11g中与10g的区别。

1106版本中数据泵导入REMAP_TABLE无效：1106版本中数据泵导入REMAP_TABLE无效。

获取表XML原数据的几个方法：研究了一下DBMS_METADATA包。

获取表DDL的几个方法：描述一下获取DDL的方法。

小议DBMS_METADATA包的用法：对DBMS_METADATA包进行深入一些的描述。

利用数据泵实现表空间迁移：使用数据泵进行表空间迁移。

利用数据泵实现表空间迁移（二）：通过NETWORK_LINK的方式进行表空间的迁移。

TRANSPORTABLE方式导入需要处理不必要的对象：TRANSPORTABLE方式导出和导入还存在一些小问题。

同一个表空间能否被多个数据库同时打开（二）：迁移表空间的方式在两个数据库加载同一个表空间。

同一个表空间能否被多个数据库同时打开（三）：两个数据库加载同一个数据文件后，能否被一个或两个数据库只读打开。

同一个表空间能否被多个数据库同时打开（四）：继续探讨以可写方式打开同时加载到两个数据库的同一个数据文件。

数据泵导入报错ORA-39125和LPX-00225：在数据泵导入执行到表统计信息阶段，出现了这个错误。

获取克隆用户脚本的方法：通过数据泵的功能获取用户信息。

对比数据泵与原始导入导出工具（一）：从总体上描述一下二者的相同和不同之处。

对比数据泵与原始导入导出工具（二）：描述数据泵的DIRECTORY、DUMPFILE和LOGFILE参数。

对比数据泵与原始导入导出工具（三）：描述数据泵的CONTENT参数。

对比数据泵与原始导入导出工具（四）：描述数据泵的INCLUDE参数。

对比数据泵与原始导入导出工具（五）：描述数据泵的EXCLUDE参数。

数据泵避免个别表数据的导出：通过EXLUDE的方式避免个别表的数据的导出。

数据泵避免个别表数据的导出（二）：介绍如何对分区表实现这个功能。

Oracle11gr2数据泵新特性（一）：介绍数据泵的Legacy模式。

Oracle11gr2数据泵新特性（二）：介绍数据泵对表导出方式的改进。

Oracle11gr2数据泵新特性（三）：介绍数据泵对表模糊匹配导出的改进。

Oracle11gr2数据泵新特性（四）：介绍数据泵导入新增的DATA_OPTIONS参数。

Oracle11gr2数据泵新特性（五）：介绍数据泵对版本的支持。

数据泵导入时出现ORA-600(klaprs_11)错误：导出文件损坏造成的错误。







SQLPLUS:

使用SQLPLUS将输出写入文件：利用SPOOL功能将查询信息输出到文件。

COPY命令：SQLPLUS的COPY命令。

SQLPLUS内部错误1075：介绍一个SQLPLUS的bug。

AUTOTRACE的bug：Oracle客户端和SERVER端版本不一致造成的bug。

Oracle9i sqlplusw的登陆bug：9i的sqlplusw登陆bug，10g已经解决。

Oracle10g sqlplus小改进：10g的sqlplus在执行connect命令时自动调用glogin.sql实现初始化。

10G SQLPLUS CLEAR SCREEN BUG：sqlplus清屏的小bug。

ORACLE817上对同义词执行DESC出现CORE DUMP：ORACLE817上对同义词执行DESC出现CORE DUMP。

11g启动sqlplus报错没有权限：Enterprise Linux 5的SELinux安全设置导致的问题。

获取导致导入失败的数据（四）：继续探讨导致SQLPLUS程序崩溃的原因。

10G SQLPLUSW的ACCEPT命令bug：Oracle10gSqlplusw的ACCEPT命令存在bug。

Sqlplus for Solaris上SPOOL OUT的bug：Solaris平台上SQLPLUS的一个小bug。

前台ORA-1075错误后台ORA-18错误：使用SYSDBA连接数据库报错ORA-1075，但是后台alert文件中显示的错误信息为ORA-18。

11g SQLPLUS新增SHOW SPPARAMETER命令：Oracle新增了SHOW SPPARAMETER命令，使得查询来自SPFILE文件的初始化参数更加容易。

SQLPLUS连接告警“未加载产品用户概要文件信息”：这个错误一般在手工建立的数据库中容易碰到。

限制SQLPLUS工具可执行的命令：利用SQLPLUS的功能禁止指定命令的执行。

10g Sqlplus工具登陆小改进：SQLPLUS / AS SYSDBA登陆的改进。

[6]