原文連結：oracle之spool詳細使用總結 - zmlctt - 部落格園
今天實際項目中用到了spool，發現網上好多内容不是很全，自己摸索了好半天，現在總結一下。
一、通過spool 指令，可以将select 資料庫的内容寫到檔案中，通過在sqlplus設定一些參數，使得按指定方式寫到檔案中
（1）正常使用spool方法，将set的一些指令和spool，select等放入.sql腳本中，然後再sqlplus中運作該腳本。以下為logmnr.sql腳本，
在sqlplus中執行@logmnr.sql就可以寫入檔案record3.txt中。不會再終端顯示任何資訊。但是，如果是在sqlplus中輸入：
set termout off;
......
spool record3.txt
select ....... from .....;
spool off;
前面的設定是沒有用的，還是會在終端中顯示大量資訊。
set echo off;
set heading off;
set line 100;
set long 2000000000;
set longchunksize 255;
set wra on;
set newpage none;
set pagesize 0;
set numwidth 12;
set termout off;
set trimout on;
set trimspool on;
set feedback off;
set timing on;
execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/app/oracle/logs/hrbfct_1_4156_748575599.arc',Options=>dbms_logmnr.new);
execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/app/oracle/logs/hrbfct_2_6645_748575599.arc',Options=>dbms_logmnr.addfile);
execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/app/oracle/logs/dict.ora');
spool /oracle/app/oracle/logs/record3.txt;
select to_clob(sql_redo)||'|'||to_char(scn)||'|'||to_char(timestamp)||'|'||to_char(session_info)||'|'||to_char(table_name)||'|'||to_char(seg_owner)||'?'
from v$logmnr_contents;
spool off;
exit;
（2）那到底能否在shell腳本中運作還不顯示這些資訊呢，答案是有的。例如
#!/bin/ksh
echo "set echo off;
set heading off;
set line 100;
set long 2000000000;
set longchunksize 255;
set wra on;
set newpage none;
set pagesize 0;
set numwidth 12;
set termout off;
set trimout on;
set trimspool on;
set feedback off;
set timing on;
execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/app/oracle/logs/hrbfct_1_4156_748575599.arc',Options=>dbms_logmnr.new);
execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/app/oracle/logs/dict.ora');
spool /oracle/app/oracle/logs/record3.txt;
select to_clob(sql_redo)||'|'||to_char(scn)||'|'||to_char(timestamp)||'|'||to_char(session_info)||'|'||to_char(table_name)||'|'||to_char(seg_owner)||'?'
from v\$logmnr_contents;
spool off;
" | sqlplus '/as sysdba'>/dev/null
這樣就能利用shell腳本中執行spool方法，同時不會再終端中顯示。注意，隻有這種方法可以。
試過這種方法，結果證明是不行的。。。。 看着和上面echo進去很像，但事實就是不行，還是會顯示大量的資訊，兩個！就是将中間内容發送到sqlplus中作為輸入
#！/bin/bash
......
.....
sqlplus oracleuser/[email protected]_NAME << !
set ECHO off
set heading off
set pagesize 0
set linesize 1000
set term off
set trims on
set feedback off
spool $tmpfile
select owner||'.'||table_name||',' from all_tables where owner=upper('$owner_user') and table_name like 'DR%$exp_month%';
spool off
quit
!
.......
（2）spool通常會用到連接配接||，這裡講一下連接配接是怎麼回事
SQL> SELECT LPAD('x',4000,'x') || LPAD('x',4000,'x') || LPAD('x',4000,'x') FROM DUAL;
SELECT LPAD('x',4000,'x') || LPAD('x',4000,'x') || LPAD('x',4000,'x') FROM DUAL
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
這裡簡單先介紹下lpad和rpad是怎麼回事：（l，r隻是方向不同）
rpad函數從右邊對字元串使用指定的字元進行填充
rpad(string,padded_length,[pad_string])
string 表示：被填充的字元串
padded_length 表示：字元的長度，是傳回的字元串的數量，如果這個數量比原字元串的長度要短，rpad函數将會把字元串截取成從左到右的n個字元;
pad_string 是個可選參數，這個字元串是要粘貼到string的右邊，如果這個參數未寫，lpad函數将會在string的右邊粘貼空格。
例如：
rpad('tech', 7); 将傳回'tech '
rpad('tech', 2); 将傳回'te'
rpad('tech', 8, '0'); 将傳回'tech0000'
rpad('tech on the net', 15, 'z'); 将傳回 'tech on the net'
rpad('tech on the net', 16, 'z'); 将傳回 'tech on the netz'
好了，現在回到上面的問題，為什麼會出錯呢，因為varchar2在oracle中，最多隻支援到4000個字元，也就是32K，||的操作會把後面的放入到前面裡，就是把後面4000個x放入到
前一個4000個x裡，作為varchar2，當然就超過了4000的界限。
Problem Description: The problem with this query is with the use of CONCAT operator (||). e.g.: select char1 || char2 from dual Concat operator returns char1 concatenated with char2. The string returned is in the same character set as char1. So here concat operator is trying to return varchar2, which has limit of 4000 characters and getting exceeded. This problem may also come when we try to CONCAT a VARCHAR2 with CLOB. e.g.: select char1 || clob from dual So here we can simply convert its first string to CLOB and avoid this error. After converting first string to CLOB, CONCAT operator will return string of CLOB type
Solution: SELECT TO_CLOB(LPAD('x',4000,'x')) || LPAD('x',4000,'x') || LPAD('x',4000,'x') FROM DUAL
是以問題解決了，隻需要将連接配接的第一個轉換成clob就可以。
看我上面的logminer.sql，我将sql_redo用to_clob函數轉換成了clob類型，如果不設定set long 20000000和set longchunksize 255;就會發現，在record3文檔中每行隻有
前80個字元，剩下的都被截斷了，這就是我上篇部落格中的clob截斷問題，是以用上篇部落格的方法可以完美解決問題。歐耶！
（3）shell調用spool 的另一種方法，
那就是在shell中調用
selecttpmof03.txt set heading off
set feedback off
set echo off
set newp none
set termout off
spool /home/orarun/scripts/date.txt
select a.REC_CREATOR||'|'||a.REC_CREATE_TIME||'|'||b.event_name||'|'||a.ORDER_NO||'|'||a.MAT_NO||'|'||a.MAT_STATUS||'|'||a.WT from tpmof03 a,tpmof21 b where (a.event_id=b.event_id and a.event_id in('52','6A','6B','6C','6D','5B'))and (a.rec_create_time>='20120101000000');
spool off;
#!/bin/sh
rm /home/orarun/scripts/date.txt
sqlplus tjc1/[email protected] << EOF
@selecttpmof03.txt #或者sqlplus 。。。。。。。。@logminer.sql
EOF
這種方法理論上也不會在終端上顯示資訊，不知道為啥上面那個用！的就不行，感覺差不多的樣子
（4）還有一種想法可以在shell腳本中編寫出一個.sql腳本，然後去執行它。
#!/bin/ksh
record=/oracle/app/oracle/logs/dirct
flag=0
count=1
echo "set echo off;
set heading off;
set line 100;
set long 2000000000;
set longchunksize 255;
set wra on;
set newpage none;
set pagesize 0;
set numwidth 12;
set termout off;
set trimout on;
set trimspool on;
set feedback off;
set timing on;" > logmnr.sql
echo "write config"
for file_i in `cat $record`;
do
flag=1
if [ $count -eq 1 ];then
sed -i '/'''$file_i'''/d' $record
echo "execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/app/oracle/logs/$file_i',Options=>dbms_logmnr.new);">>logmnr.sql
count=0
else
sed -i '/'''$file_i'''/d' $record
echo "execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/app/oracle/logs/$file_i',Options=>dbms_logmnr.addfile);">>logmnr.sql
fi
done
echo "execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/app/oracle/logs/dict.ora');">>logmnr.sql
if [ $flag -eq 1 ];then
echo "spool /oracle/app/oracle/logs/record3.txt;
select to_clob(sql_redo)||'|'||to_char(scn)||'|'||to_char(timestamp)||'|'||to_char(session_info)||'|'||to_char(table_name)||'|'||to_char(seg_owner)||'?'
from v\$logmnr_contents;
spool off;
exit;">>logmnr.sql
#sqlplus '/as sysdba' @logmnr.sql
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)