Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: July 11, 2017 - 1:48 am UTC

Last updated: July 11, 2017 - 10:31 am UTC

Version: SQL*Plus: Release 10.2.0.5.0

Viewed 10K+ times! This question is

You Asked

After run a script for check my db tablespaces, the error log is SP2-0308: cannot close spool file, how to solve the problem?

+ + date +%Y%m%d
today=20170710
+ . /export/home/oracle/.profile
+ 1> /dev/null 2>& 1
+ sqlplus -s /as sysdba
+ 0<<

spool /export/home/oracle/logs/DDMSDB_tablespace_20170710.txt

col tablespace_name for a18
set pagesize 100
select a.tablespace_name tablespace_name,
nvl(ceil((1 - b.free / a.total) * 100),100) "usage_of_tablespace%",
nvl(b.free,0) "left_space(M)",
c.EXTENT_MANAGEMENT "Extent_management"
from (select tablespace_name, sum(nvl(bytes, 0)) / 1024 / 1024 total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(nvl(bytes, 0)) / 1024 / 1024 free
from dba_free_space
group by tablespace_name) b,
dba_tablespaces c
where a.tablespace_name = c.tablespace_name
and c.tablespace_name = b.tablespace_name(+)
order by (1 - b.free / a.total) * 100 desc;

quit

TABLESPACE_NAME usage_of_tablespace% left_space(M) Extent_man
------------------ -------------------- ------------- ----------
UNDOTBS1 63 12945.0625 LOCAL
LARGE_BOSTON_INDEX 55 161280 LOCAL
LARGE_BOSTON 49 176896 LOCAL
BOSTON 46 4456 LOCAL
SYSAUX 35 670.3125 LOCAL
BOSTON_INDEX 23 20684 LOCAL
SYSTEM 22 803.773438 DICTIONARY

7 rows selected.

SP2-0308: cannot close spool file
+ mailx -s DDMSDB TABLESPACE STATISTICS wu_qi_yao@asmc.com.cn
+ 0< /export/home/oracle/logs/DDMSDB_tablespace_20170710.txt
+ mailx -s DDMSDB TABLESPACE STATISTICS xin_rong_wu@asmc.com.cn
+ 0< /export/home/oracle/logs/DDMSDB_tablespace_20170710.txt
+ mailx -s DDMSDB TABLESPACE STATISTICS dan_ji_zhan@asmc.com.cn
+ 0< /export/home/oracle/logs/DDMSDB_tablespace_20170710.txt
+ echo logout
logout

and Chris said...

A few things to check:

- Is something else opening the file while you're spooling to it?
- There space on the file system you're spooling to?
- Could you be hitting BUG 4478017 - set LINESIZE less than the lrecl of the spool file? This affects SQL*Plus - Version: 10.1.0.2 to 10.2.0.3 - Release: Oracle10g to Oracle10g, IBM z/OS on System z. See MOS note 375516.1.


Is this answer out of date? If it is, please let us know via a Comment