Oracle Spool详解_SukerYan

http://blog.sina.com.cn/u/1808592950

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

Oracle Spool详解

(2013-03-13 10:53:00)

标签：

oracle

it

本文来自iDB Stock:http://www.idb-stock.net/idb/2011/06/01/153.html

1.spool的作用是什么？

spool的作用可以用一句话来描述：在sqlplus中用来保存或打印查询结果。

2.spool在oracle 9i、10g和11g下的语法及使用示例

oracle 9i spool语法
SPO[OL] [file_name[.ext]|OFF|OUT]

oracle 10g、11g spool语法
SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

oracle 9i spool的主法比较简单，其实就相当于oracle 10g中spool的replace选项，因此本文主要介绍oracle 10g的选项。

file_name[.txt]
保存查询结果集的的路径和文件名，如果没有指定后缀名，默认名一般为.lst或.lis。如果指定系统文件为/dev/null and /dev/stderr，则不会添加后缀名。

off
完成spool。

out
停止spool,并将文件输出到终端设备上，如打印机(可能有些操作系统不支持)。

我们从语法上可以看到，oracle在10g、11g中对spool增加了create、replace、append选项，
create
    创建指定文件名的新文件；如指定文件存在，则报文件存在错误。
replace
    如果指定文件存在则覆盖替换；如指定文件不存在，则创建，replace为spool默认选项。
append
    向指定文件名中追加内容；如指定文件不存在,则创建。

sqlplus spool的create、replace、append选项示例

spool spool.ora create

spool spool.ora repalce

spool spool.ora append

3.利用sqlplus spool将表导成csv文件

本sql将dba_sequence的所有数据导成csv文件。

view
plaincopy to
clipboardprint?

SET HEADING OFF  

SET ECHO OFF  

SET FEEDBACK OFF  

SET PAGES 0  

SET LINESIZE 32766  

SET LONG 1999999  

SET TRIMOUT ON  

SET TRIMSPOOL ON  

SET NEWPAGE NONE  

SET SQLBLANKLINES OFF  

SET TRIMS ON  

SET TIMING OFF  

SET SERVEROUTPUT ON  

SET VERIFY OFF  

COLUMN SQL_TEXT FORMAT A32766 WORD WRAPPED  

SPOOL data_to_csv.sql  

SELECT 'SELECT ''"''||' ||  

       REPLACE(wmsys.wm_concat(COLUMN_NAME), ',', '||''","''||'||chr(10)) ||  

       '||''"'' SQL_TEXT FROM DBA_SEQUENCES;' SQL_TEXT  

  FROM DBA_TAB_COLUMNS  

 WHERE TABLE_NAME = 'DBA_SEQUENCES'  

 order by column_id;  

spool off  

spool data_to_csv.csv  

@data_to_csv.sql  

spool off

运行如下：

17:10:26 dw@dw>@A.SQL
SELECT '"'||SEQUENCE_OWNER||'","'||
SEQUENCE_NAME||'","'||
MIN_VALUE||'","'||
MAX_VALUE||'","'||
INCREMENT_BY||'","'||
CYCLE_FLAG||'","'||
ORDER_FLAG||'","'||
CACHE_SIZE||'","'||
LAST_NUMBER||'"' SQL_TEXT FROM DBA_SEQUENCES;
















"SYS","UGROUP_SEQUENCE","0","999999999999999999999999999","1","N","Y","10","1"
"SYS","OBJECT_GRANT","1","999999999999999999999999999","1","N","Y","20","26432"
"SYS","SYSTEM_GRANT","1","999999999999999999999999999","1","N","Y","20","1046"
"SYS","PROFNUM$","0","999999999999999999999999999","1","N","N","0","2"
"SYS","AUDSES$","1","2000000000","1","Y","N","10000","581721"
"SYS","SNAPSHOT_ID$","1","2147483647","1","N","N","20","4"
"SYS","SNAPSITE_ID$","1","4294967295","1","N","N","20","1"
"SYS","JOBSEQ","1","999999999","1","Y","N","20","21"
"SYS","RGROUPSEQ","1","999999999","1","Y","N","20","1"
"SYS","ORA_TQ_BASE$","1","4294967","1","Y","N","0","83"
"SYS","PARTITION_NAME$","1","999999999999999999999999999","1","N","N","20","21"
"SYS","CDC_SUBSCRIBE_SEQ$","1","999999999999999999999999999","1","N","N","20","1"
"SYS","CDC_RSID_SEQ$","1","999999999999999999999999999","1","N","Y","10000","10001"
"SYS","LOG$SEQUENCE","0","999999999999999999999999999","1","N","Y","10","1"
"SYS","PSINDEX_SEQ$","1","18446744073709551615","1","N","N","1000","2100"
"SYS","AWSEQ$","1","4294967295","1","N","N","0","1000"
"SYS","STREAMS$_CAPTURE_INST","1","4294967295","1","Y","N","0","1"
.....

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：高清播放器通过SMB访问win7里的共享文件设置方法

后一篇：mysql远程访问方法

新浪BLOG意见反馈留言板　欢迎批评指正