Home » Infrastructure » Linux » Oracle temp_space_header: parameter not set (sqlplus Oracle 11.2.0.4 on Redhat 6.5)
icon4.gif  Oracle temp_space_header: parameter not set [message #653014] Thu, 23 June 2016 11:41 Go to next message
ma251436
Messages: 5
Registered: June 2015
Location: USA
Junior Member
I am getting this error:

chkdbSpace.ksh[5]: temp_space_header: parameter not set

when running the following sql as follows:

#! /bin/ksh

rptFile=/home/depot/exports/chkdbSpace.rpt

sqlplus -s nm99/nm9999 << -
clear columns
column tablespace format a30
column total_mb format 999,999,999.99
column used_mb format 999,999,999,999.99
column free_mb format 999,999,999.99
column pct_used format 999.99
column status format a10
compute sum of total_mb on report
compute sum of used_mb on report
compute sum of free_mb on report
break on report
SET WRAP OFF
SET FEEDBACK OFF
SET ECHO OFF
SET LINESIZE 130
SET PAGESIZE 50
SPOOL /home/depot/exports/chkdbSpace.rpt
select  total.ts tablespace,
        DECODE(total.mb,null,'OFFLINE',dbat.status) status,
        total.mb total_mb,
        NVL(total.mb - free.mb,total.mb) used_mb,
        NVL(free.mb,0) free_mb,
        DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used
from
        (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
        (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free,
        dba_tablespaces dbat
where total.ts=free.ts(+) and
      total.ts=dbat.tablespace_name
UNION ALL
select  sh.tablespace_name,
        'TEMP',
        SUM(sh.bytes_used+sh.bytes_free)/1024/1024 total_mb,
        SUM(sh.bytes_used)/1024/1024 used_mb,
        SUM(sh.bytes_free)/1024/1024 free_mb,
        ROUND(SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free)*100,2) pct_used

FROM v$temp_space_header sh
GROUP BY tablespace_name
order by 6
/
ttitle off
SPOOL OFF
rem clear columns
exit;
-
cat $rptFile | mailx -s "DB space Report" johndo@yahoo.com
exit 0

[Updated on: Thu, 23 June 2016 11:56]

Report message to a moderator

Re: Oracle temp_space_header: parameter not set [message #653015 is a reply to message #653014] Thu, 23 June 2016 11:56 Go to previous messageGo to next message
BlackSwan
Messages: 25744
Registered: January 2009
Location: SoCal
Senior Member
>FROM v$temp_space_header sh
not like above but like below
FROM v\$temp_space_header sh
Re: Oracle temp_space_header: parameter not set [message #653016 is a reply to message #653015] Thu, 23 June 2016 12:01 Go to previous messageGo to next message
ma251436
Messages: 5
Registered: June 2015
Location: USA
Junior Member
Perfect. it is working now. The odd thing is if I run the sql after I manually login to sqlplus it works fine. Only when I run it as batch sqlplus in a ksh script it expects it to be v\$temp..etc. I wonder why !

Thank you so much for the fix. Smile

[Updated on: Thu, 23 June 2016 12:02]

Report message to a moderator

Re: Oracle temp_space_header: parameter not set [message #653017 is a reply to message #653016] Thu, 23 June 2016 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 25744
Registered: January 2009
Location: SoCal
Senior Member
ma251436 wrote on Thu, 23 June 2016 10:01
Perfect. it is working now. The odd thing is if I run the sql after I manually login to sqlplus it works fine. Only when I run it as batch sqlplus in a ksh script it expects it to be v\$temp..etc. I wonder why !

Thank you so much for the fix. Smile


the posted code runs as a "here script"; which means that it is processed by the bash shell & the shell treats $temp_space_header as an environmental variable.
The preceding back-slash informs the shell to treat the dollar-sign as a literal character & not as the start of a shell variable.
Re: Oracle temp_space_header: parameter not set [message #653018 is a reply to message #653017] Thu, 23 June 2016 12:25 Go to previous message
ma251436
Messages: 5
Registered: June 2015
Location: USA
Junior Member
Thank you
Previous Topic: ask Linux Oracle confirmation
Next Topic: no more data to read from socket error
Goto Forum:
  


Current Time: Tue Dec 12 12:59:30 CST 2017

Total time taken to generate the page: 0.42386 seconds