Oracle's spool detailed usage summary

Today the actual project used the spool, found online a lot of content is not very full, their own groping long time, now summed up.

One, through the spool command, you can write the contents of the Select database to a file, by setting some parameters in Sqlplus, so as to write to the file in the specified way

(1) General use the Spool method, set some commands and spool,select, etc. into the. SQL script, and then run the script in Sqlplus. The following is the Logmnr.sql script,

Execution of @logmnr.sql in Sqlplus can be written to the file record3.txt. No more information will be displayed on the terminal. However, if you are entering in Sqlplus:

Set Termout off;

......

Spool Record3.txt

Select ..... from ...;

Spool off;

The previous setting is not available, or it will display a lot of information in the terminal.

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); Execute dbms_logmnr.add_logfile (logfilename=> '/oracle/app/oracle/logs/hrbfct_2_6645_ 748575599.arc ', options=>dbms_logmnr.addfile); DBMS_LOGMNR.START_LOGMNR execute (dictfilename=> '/oracle/ App/oracle/logs/dict.ora '); 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) | | '? ' From v$logmnr_contents;21 spool off;22 exit;

(2) Whether it is possible to run in a shell script without displaying this information, the answer is yes.
For example

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 Ti Ming on;16 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;19 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;21 spool off;22 "| Sqlplus '/as sysdba ' >/dev/null

This makes it possible to execute the spool method in a shell script without displaying it in the terminal. Note that only this method is possible.

Try this method, the results prove that it is not possible .... Look at and above echo go in very much like, but the fact is not, still will show a lot of information, two! is to send the intermediate content to the Sqlplus as input

1 #! /bin/bash 2 ... 3 ..... 4 sqlplus oracleuser/[email protected]_name <<! 5 Set ECHO off 6 set heading off 7 set pagesize 0 8 set linesize 9 set term OFF10 set trims On11 set feedback off12 s Pool $tmpfile Select Owner| | | table_name| | ', ' from All_tables where Owner=upper (' $owner _user ') and table_name like ' dr% $exp _month% '; Spool Off15 quit 16!17 ....

(2) Spool usually uses a connection | |, here's what the connection is about.

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

Here is a brief introduction of the next Lpad and Rpad is going to be: (L,r just different direction)
The Rpad function fills the string with the specified character from the right
Rpad (string,padded_length,[pad_string])
String representation: the Filled strings
Padded_length: The length of the character is the number of strings returned, and if this number is shorter than the length of the original string, the Rpad function will intercept the string as a left-to-right n character;
Pad_string is an optional argument that is to be pasted to the right of the string, and if the parameter is not written, the Lpad function will paste a space on the right side of the string.
For example:
Rpad (' Tech ', 7); will return ' tech '
Rpad (' Tech ', 2); will return ' Te '
Rpad (' Tech ', 8, ' 0 '); will return ' tech0000 '
Rpad (' Tech on the net ', ' Z '); Will return ' Tech on the net '
Rpad (' Tech on the net ', +, ' z '); Will return ' tech on the Netz '

Well, now back to the question above, why would it be wrong, because varchar2 in Oracle, up to 4,000 characters, i.e. 32k,| | The operation will put the back into the front, that is, the back 4,000 x into the
The previous 4,000 x, as VARCHAR2, of course, exceeded the 4000 limit.

problem Description:The problem with the 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 are trying to return VARCHAR2, which have limit of 4000 characters and getting exceeded. This problem may also come if we try to CONCAT a VARCHAR2 with clob.e.g.: Select Char1 | | Clob from Dualso Here we can simply convert it first string to CLOB and avoid this error. After converting first string to CLOB, CONCAT operator would return string of CLOB typeSolution:SELECT To_clob (Lpad (' x ', 4000, ' x ')) | | Lpad (' x ', 4000, ' x ') | | Lpad (' x ', 4000, ' X ') from DUAL
So the problem is solved by simply converting the first of the connections into CLOB.

Looking at my logminer.sql, I sql_redo the To_clob function into the CLOB type, if set long 20000000 and set Longchunksize 255 are not setting, you will find that in the Record3 document each line is only
The first 80 characters, the rest is truncated, this is my last blog in the Clob truncation problem, so use the method of the previous blog to solve the problem perfectly. Oh yes

(3) Another way for the shell to invoke spool,
That's called in the 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 SE Lect 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 (' ', ' 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/[email protected] << EOF6 7 @selecttpmof03. txt                    # Or sqlplus ... @logminer. Sql8 9 EOF

This method in theory will not display information on the terminal, do not know why the above use! It doesn't work, it feels the same way.

(4) There's another idea. You can write a. sql script in a shell script and then go to execute it.

 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 T  Rimout on;16 Set Trimspool on;17 Set feedback off;18 set timing on; "> logmnr.sql19 echo" write config "--file_i in ' Cat $record '; Do22 flag=123 If [$count-eq 1];then24 sed-i '/' $file _i '/d ' $record echo "Execute Dbms_logmnr.add_logfile (LOGF Ilename=> '/oracle/app/oracle/logs/$file _i ', options=>dbms_logmnr.new); " >>logmnr.sql26 count=027 else28 sed-i '/' $file _i ', '/d ' $record 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) | | '? ' From v\ $LOGMNR _contents;37 spool off;38 exit; " >>logmnr.sql39 #sqlplus '/as sysdba ' @logmnr. sql

Http://www.cnblogs.com/zmlctt/p/3721188.html

Oracle's Spool detailed usage summary (RPM)