Spooling Records From SQL*Plus Appears To Be Hung, But Actually Taking Too Long (Doc ID 781447.1)

Last updated on OCTOBER 31, 2013

Applies to:

SQL*Plus - Version 10.2.0.2 to 10.2.0.4 [Release Oracle10g]
Information in this document applies to any platform.
***Checked for relevance on 31-Oct-2013***

Symptoms

When spooling a table to an ascii file, it takes a long time to complete. This is noticed when SPOOL command is used together with TRIMSPOOL and LINESIZE parameters.

For example, consider this script which is used to spool data from SQL*Plus:

spool_file.sh
 

echo "[start] executing {$sql_script} at `date`\n"
sqlplus /nolog <whenever sqlerror exit 2
conn scott/tiger
@emp_spool.sql
END

echo""
echo "[end] executing {sql_script} at `date`\n"


emp_spool.sql

WHENEVER OSERROR EXIT 10
WHENEVER SQLERROR EXIT 20

set pagesize 0
set linesize 10000
set echo off
set feed off
set term off
set heading off
set trims on

spool /tmp/count.log
select a.empno, a.ename, a.sal, a.deptno
from emp_test_hang a, emp_test_hang b
where rownum <200000;

spool off
exit
/;



Execute this sample script as follows:

#spool_file.sh >> /tmp/out.log


Once script is executed, it is observed that the spool file (count.log) gets created far earlier than the
script execution time (out.log). There is a considerable time difference between spool completion and script completion.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms