oracle之spool详细使用总结-白红宇

oracle之spool详细使用总结

阅读量：5758 次

发布时间：2019-06-18

本文共 6457 字，大约阅读时间需要 21 分钟。

今天实际项目中用到了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;

前面的设置是没有用的，还是会在终端中显示大量信息。

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 $tmpfile13 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 dualConcat 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 dualSo 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 typeSolution: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 off6 spool /home/orarun/scripts/date.txt7 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');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=123 if [ $count -eq 1 ];then24 sed -i '/'''$file_i'''/d' $record25 echo "execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/app/oracle/logs/$file_i',Options=>dbms_logmnr.new);">>logmnr.sql26 count=027 else28 sed -i '/'''$file_i'''/d' $record29 echo "execute dbms_logmnr.add_logfile(LogFileName=>'/oracle/app/oracle/logs/$file_i',Options=>dbms_logmnr.addfile);">>logmnr.sql30 fi31 done32 echo "execute dbms_logmnr.start_logmnr(DictFileName=>'/oracle/app/oracle/logs/dict.ora');">>logmnr.sql33 if [ $flag -eq 1 ];then34 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.sql39 #sqlplus '/as sysdba' @logmnr.sql

转载于:https://www.cnblogs.com/zmlctt/p/3721188.html

你可能感兴趣的文章