Oracle spool learning Summary

Oracle spool learning summary common spool settings set colsep ''; // set echo off, a domain output separator; // display each SQL command in the start script. The default value is onset feedback off; // display the number of records processed by this SQL command. The default value is onset heading off. // The output field title. The default value is onset pagesize 0. // output the number of rows per page, the default value is 24. To avoid paging, you can set it to 0. Set termout off; // display the execution results of commands in the script. The default value is onset trimout on. // Remove trailing spaces of each standard output line. The default value is offset trimspool on; // remove the trailing space of the redirection (spool) output line. The default value is off. Note: The LINESIZE must be slightly larger to avoid data truncation, it should be used with the corresponding TRIMSPOOL to prevent the exported text from having too many trailing spaces. However, if the LINESIZE is too large, the export speed will be greatly reduced. In general, we use the SPOOL method to export the tables in the database as text files in two ways, for example: www.2cto.com Method 1: use the following format script set colsep '|' ------ set the column separator set trimspool on set linesize 20000 set pagesize 0 set newpage 1 set heading off set term off spool path + file name select * from tablename; spool off Method 2: use the following script set trimspool on set linesize 20000 set pagesize 0 set newpage 1 set heading off set term off spool path + file name select col1 | '| col2 |' | col3 | '| Col4 from tablename; spool off compare the above methods, that is, method 1 uses the set separator and sqlplus uses the set separator to separate the fields, method 2: concatenate the Separator in the SELECT statement to manually control the output format. In practice, the data imported through method 1 has a great deal of uncertainty. The possibility of errors when the data imported by this method is then imported by sqlldr is more than 95%, this is especially true for a large number of data tables, such as tables with 1 million records, and the exported data files are incredibly large.
The format of the data file exported in method 2 is quite regular, and the size of the data file may be about 1/4 of that in method 1. When the data files exported by this method are imported by sqlldr, there is little possibility of errors, and the data files can be imported successfully. Therefore, it is recommended that you use method 2 to manually control the format of the spool file, which can reduce the possibility of errors and avoid many detours. Www.2cto.com SQL code set pagesize 0 feedback off verify off heading off set time off echo off set pagesize 0 set linesize 2000 set trims on set feedback off set TERMOUT OFF alter session set nls_date_format = 'yyyy- HH24 MM-DD: MI: ss'; spool sp_test.txt select PK_ID | '| GRID_ID | '... 'from tmp_jy_sys_table_col; spool off