Hello all
I'm trying to find a way of spooling the output of a sql command into a OS variable declared before the sql script is run.
Here is my script (I have a cms web pages which reads the tmp file created from the script).
#!/bin/ksh
#
CENTRAL_LOCATION=/CENTRAL_LOC/DBINFO/"`uname -n | awk -F. '{print $1}' | tr '[a-z]' '[A-Z]'`"_$ORACLE_SID"_dbinfo.tmp"
### SQL ###
sqlplus -S <<EOF
conn / as sysdba
set echo off
--------------------------------------------------------------------- Instance Name Module
spool instance.tmp_txt
select INSTANCE_NAME from v\$instance;
spool off
--------------------------------------------------------------------- Version Module
spool version.tmp_txt
@/apps/cads_scripts/OVERNIGHT_SCRIPTS/get_version.sql
/
spool off
--------------------------------------------------------------------- Startup Time Module
spool startup_time.tmp_txt
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM sys.v_\$instance;
spool off
--------------------------------------------------------------------- Redo Size Module
spool redo_size.tmp_txt
select ROUND(sum(bytes) /1024/1024,2) "Redo Size"
from sys.v_\$log;
spool off
--------------------------------------------------------------------- Temp Size Module
spool temp_size.tmp_txt
select ROUND(nvl(sum(bytes),0)/1024/1024,2) "Temp Size"
from dba_temp_files;
spool off
--------------------------------------------------------------------- Data Size Module
spool data_size.tmp_txt
select ROUND(sum(bytes) /1024/1024/1024,2) "Data Size"
from dba_data_files;
spool off
--------------------------------------------------------------------- Total Size Module
spool total_size.tmp_txt
select a.data_size+b.temp_size+c.redo_size "Total Database Size"
from ( select ROUND(sum(bytes) /1024/1024/1024,2) data_size
from dba_data_files ) a,
( select ROUND(nvl(sum(bytes),0)/1024/1024/1024,2) temp_size
from dba_temp_files ) b,
( select ROUND(sum(bytes) /1024/1024/1024,2) redo_size
from sys.v_\$log ) c;
spool off
-------------------------------------------------------------------- Session Module
spool sessions.tmp_txt
select count(*) from v\$session;
spool off
-----------------
spool max_sessions.tmp_txt
SELECT value FROM v\$parameter WHERE name = 'sessions';
spool off
-------------------------------------------------------------------- Archive Log Module
spool log_mode.tmp_txt
select log_mode
from v\$database;
spool off
-----------------------------------------------------------------------------------------
exit
EOF
HOST=`uname -n | awk -F. '{print $1}' | tr '[a-z]' '[A-Z]'`
INSTANCE=`awk ' NR>3 ' instance.tmp_txt`
VERSION=`head -1 version.tmp_txt`
STARTUP_TIME=`awk ' NR>3 ' startup_time.tmp_txt`
REDO_SIZE=`awk ' NR>3 ' redo_size.tmp_txt`
TEMP_SIZE=`awk ' NR>3 ' temp_size.tmp_txt`
DATA_SIZE=`awk ' NR>3 ' data_size.tmp_txt`
TOTAL_SIZE=`awk ' NR>3 ' total_size.tmp_txt`
SESSION=`awk ' NR>3 ' sessions.tmp_txt`
MAX_SESSIONS=`awk ' NR>3 ' max_sessions.tmp_txt`
LOG_MODE=`awk ' NR>3 ' log_mode.tmp_txt`
rm *.tmp_txt
echo "|""[["$HOST"]]""|""[["$INSTANCE"]]""|"$VERSION"|" $STARTUP_TIME "|" $REDO_SIZE "|" $TEMP_SIZE "|" $DATA_SIZE "|" $TOTAL_SIZE "|" $SESSION"/ "$MAX_SESSIONS "|" $LOG_MODE "|" > $CENTRAL_LOCATION
Unfortunately, there seems to be a file access/write conflict when running the script on servers with multipe DBs.
I wanted to pass $ORACLE_SID into the spool file to get around this issue but can't seem to get it work.
Any ideas on what I'd need to do?
TIA.