oracle之spool详细使用总结（转）

今天实际项⽬中⽤到了spool，发现⽹上好多内容不是很全，⾃⼰摸索了好半天，现在总结⼀下。

⼀、通过spool 命令，可以将select 数据库的内容写到⽂件中，通过在sqlplus设置⼀些参数，使得按指定⽅式写到⽂件中

（1）常规使⽤spool⽅法，将set的⼀些命令和spool，select等放⼊.sql脚本中，然后再sqlplus中运⾏该脚本。以下为logmnr.sql脚本，在sqlplus中执⾏@logmnr.sql就可以写⼊⽂件record3.txt中。不会再终端显⽰任何信息。但是，如果是在sqlplus中输⼊：set termout off;......

spool record3.txtselect ....... from .....;spool off;

前⾯的设置是没有⽤的，还是会在终端中显⽰⼤量信息。

1 set echo off; 2 set heading off; 3 set line 100;

4 set long 2000000000; 5 set longchunksize 255; 6 set wra on;

7 set newpage none; 8 set pagesize 0; 9 set numwidth 12;10 set termout off;11 set trimout on;12 set trimspool on;13 set feedback off;14 set timing on;

15 execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/app/oracle/logs/hrbfct_1_4156_748575599.arc',Options=>dbms_logmnr.new);16 execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/app/oracle/logs/hrbfct_2_6645_748575599.arc',Options=>dbms_logmnr.addfile);17 execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/app/oracle/logs/dict.ora');18 spool /oracle/app/oracle/logs/record3.txt;

19 select to_clob(sql_redo)||'|'||to_char(scn)||'|'||to_char(timestamp)||'|'||to_char(session_info)||'|'||to_char(table_name)||'|'||to_char(seg_owner)||'?'20 from v$logmnr_contents;21 spool off;22 exit;

（2）那到底能否在shell脚本中运⾏还不显⽰这些信息呢，答案是有的。例如

1 #!/bin/ksh

2 echo \"set echo off; 3 set heading off; 4 set line 100;

5 set long 2000000000; 6 set longchunksize 255; 7 set wra on;

8 set newpage none; 9 set pagesize 0;10 set numwidth 12;11 set termout off;12 set trimout on;13 set trimspool on;14 set feedback off;15 set timing on;

16 execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/app/oracle/logs/hrbfct_1_4156_748575599.arc',Options=>dbms_logmnr.new);17 execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/app/oracle/logs/dict.ora');18 spool /oracle/app/oracle/logs/record3.txt;

19 select to_clob(sql_redo)||'|'||to_char(scn)||'|'||to_char(timestamp)||'|'||to_char(session_info)||'|'||to_char(table_name)||'|'||to_char(seg_owner)||'?'20 from v\\$logmnr_contents;21 spool off;

22 \" | sqlplus '/as sysdba'>/dev/null

这样就能利⽤shell脚本中执⾏spool⽅法，同时不会再终端中显⽰。注意，只有这种⽅法可以。

试过这种⽅法，结果证明是不⾏的。。。。 看着和上⾯echo进去很像，但事实就是不⾏，还是会显⽰⼤量的信息，两个！就是将中间内容发送到sqlplus中作为输⼊

1 #！/bin/bash 2 ...... 3 .....

4 sqlplus oracleuser/user@SERVICE_NAME << ! 5 set ECHO off 6 set heading off 7 set pagesize 0 8 set linesize 1000 9 set term off10 set trims on11 set feedback off12 spool $tmpfile

13 select owner||'.'||table_name||',' from all_tables where owner=upper('$owner_user') and table_name like 'DR%$exp_month%';14 spool off15 quit16 !17 .......

（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

1 set heading off2 set feedback off3 set echo off4 set newp none5 set termout off

6 spool /home/orarun/scripts/date.txt

7 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_8 spool off;

1 #!/bin/sh2

3 rm /home/orarun/scripts/date.txt4

5 sqlplus tjc1/tjc10804@tjc1 << EOF6

7 @selecttpmof03.txt #或者sqlplus 。。。。。。。。@logminer.sql8

9 EOF

这种⽅法理论上也不会在终端上显⽰信息，不知道为啥上⾯那个⽤！的就不⾏，感觉差不多的样⼦ （4）还有⼀种想法可以在shell脚本中编写出⼀个.sql脚本，然后去执⾏它。

1 #!/bin/ksh

2 record=/oracle/app/oracle/logs/dirct 3 flag=0 4 count=1

5 echo \"set echo off; 6 set heading off; 7 set line 100;

8 set long 2000000000; 9 set longchunksize 255;10 set wra on;

11 set newpage none;12 set pagesize 0;13 set numwidth 12;14 set termout off;15 set trimout on;16 set trimspool on;17 set feedback off;

18 set timing on;\" > logmnr.sql19 echo \"write config\"

20 for file_i in `cat $record`; 21 do22 flag=1

23 if [ $count -eq 1 ];then24 sed -i '/'''$file_i'''/d' $record

25 echo \"execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/app/oracle/logs/$file_i',Options=>dbms_logmnr.new);\">>logmnr.sql26 count=027 else

28 sed -i '/'''$file_i'''/d' $record

29 echo \"execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/app/oracle/logs/$file_i',Options=>dbms_logmnr.addfile);\">>logmnr.sql30 fi31 done

32 echo \"execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/app/oracle/logs/dict.ora');\">>logmnr.sql33 if [ $flag -eq 1 ];then

34 echo \"spool /oracle/app/oracle/logs/record3.txt;

35 select to_clob(sql_redo)||'|'||to_char(scn)||'|'||to_char(timestamp)||'|'||to_char(session_info)||'|'||to_char(table_name)||'|'||to_char(seg_owner)||'?'36 from v\\$logmnr_contents;37 spool off;

38 exit;\">>logmnr.sql

39 #sqlplus '/as sysdba' @logmnr.sql