Re: SQLPLUS Question
Date: Sat, 16 Jan 2010 23:01:50 +0100
Message-ID: <4b52368a$0$2886$ba620e4c_at_news.skynet.be>
Jeremy schreef:
> In article<4b5212b2$0$2864$ba620e4c_at_news.skynet.be>, ghp_at_skynet.be
> says...>
>> Jeremy schreef:
>>> Hi, real simple question and one I suspect the answer to which is "no".
>>>
>>> Can you create sqlplus scripts with "conditions" such that if for
>>> example a SQL statement returns a particular value or error condition
>>> then path A or path B is followed?
>>>
>>
>> I start sqlplus from a ksh script as a job, send queries to it and read
>> the answers. ksh, which is also a full programming language, takes the
>> decisions about how to continue.
>
>
> Would this be through multiple invocations of sqlplus?
>
It certainly isn't. You start one sqlplus for each database you want to connect to. Ksh can do a perfect job control. I'll show you an example. Don't listen to Gogala's rantings, if a machine considers activating a program an expensive operation, it's probably on some µicro$oft non-OS and certainly shouldn't be running a database. Gogala should brush up his knowledge of ksh, he must be talking about some pre 93 version or bsh, if he doesn't know that nowadays ksh hardly ever needs sed, ls or grep. And if I'm not mistaken, sqlplus hides the command line arguments it's been given, for some time now.
Here's an example of how I collect some database information (using a pre 93 ksh ;-). Watch out for some unwantend line breaks. This script is run as oracle, so no passwords needed. Otherwise you use environment variables to pass sensitive information.
all_db_stats.sh
# sleep seconds for measuring current activity GSWAIT=900 Usage(){
echo "Usage: $0 HOSTNAME"
if [ ! -z "$1" ]
then
echo "$1"
fi
exit $2
}
FlushPipe(){
#print -p "select 'AMEHOELA' from dual;"
print -p "prompt AMEHOELA"
while read -p
do
if [ "$REPLY" = "AMEHOELA" ]
then
break
else
if [ "$1" = "SAVE" ]
then
GSFEEDBACK[${#GSFEEDBACK[*]}]="$REPLY"
fi
fi
done
}
GetStats(){
GSHOST="$1"
GSDB="$2"
echo "Connecting to $GSDB as sysdba"
sqlplus -s /nolog |&
print -p "spool /tmp/gastat_${GSHOST}_${GSDB}"
print -p "connect / as sysdba"
print -p "set echo off"
print -p "set pages 0"
# newer versions of sqlplus no longer report "Connected."
unset GSFEEDBACK
set -A GSFEEDBACK
FlushPipe SAVE
if [ "$GSFEEDBACK" = "Connected." -o -z "$GSFEEDBACK" ] then
print -p "set feedback off"
print -p "set lines 80"
print -p "set numwidth 17"
print -p "set termout off"
print -p "set trimout on"
print -p "column R format 999999999999999"
print -p "column W format 999999999999999"
print -p "column D format a20"
print -p "spool /tmp/gastat_stats_${GSHOST}_${GSDB}"
print -p "select version from v\$instance;"
unset GSFEEDBACK
FlushPipe SAVE
GSVERSION=${GSFEEDBACK}
# try to estimate database activity
case "$GSVERSION" in
10.2.*)
print -p "select" \
" (select value from v\$sysstat" \
" where name = 'physical read bytes') R," \
" (select value from v\$sysstat" \
" where name = 'physical write bytes') W," \
" to_char(sysdate,'YYYYMMDDHH24MISS') D" \
" from dual;"
sleep $GSWAIT
print -p "select" \
" (select value from v\$sysstat" \
" where name = 'physical read bytes') R," \
" (select value from v\$sysstat" \
" where name = 'physical write bytes') W," \
" to_char(sysdate,'YYYYMMDDHH24MISS') D" \
" from dual;"
;;
*)
print -p "select" \
" sum(s.PHYBLKRD * f.block_size) R, "\
" sum(s.PHYBLKWRT * f.block_size) W, "\
" to_char(sysdate,'YYYYMMDDHH24MISS') D" \
" from v\$datafile f, v\$filestat s" \
" where s.file# = f.file#;"
sleep $GSWAIT
print -p "select" \
" sum(s.PHYBLKRD * f.block_size) R, "\
" sum(s.PHYBLKWRT * f.block_size) W, "\
" to_char(sysdate,'YYYYMMDDHH24MISS') D" \
" from v\$datafile f, v\$filestat s" \
" where s.file# = f.file#;"
print -p "SELECT" \
" Avg(BYTES) AVG#," \
" Count(1) Count#," \
" Max(BYTES) Max_Bytes," \
" Min(BYTES) Min_Bytes" \
" FROM" \
" v\$log;"
# how much logging the previous five working days?
print -p "SELECT A.Count#, Round(A.Count#*B.AVG#/1024/1024/5)" \
" Daily_Avg_Mb FROM (" \
" SELECT count(*) Count#" \
" FROM v\$log_history" \
" where first_time between" \
" next_day(trunc(sysdate),'MONDAY') - 14" \
" and next_day(trunc(sysdate),'MONDAY') - 9" \
" ) A," \
" ( SELECT Avg(BYTES) AVG# FROM v\$log) B;"
unset GSFEEDBACK
FlushPipe SAVE
X=0
while [ $X -lt ${#GSFEEDBACK[*]} ]
do
echo "Stats $X : ${GSFEEDBACK[$X]}"
(( X += 1 ))
done
GSREADBYTES1=$(expr "${GSFEEDBACK[0]}" : "\([0-9]*\)")
GSREADBYTES2=$(expr "${GSFEEDBACK[1]}" : "\([0-9]*\)")
GSWRITEBYTES1=$(expr "${GSFEEDBACK[0]}" : "[0-9]*[ ]*\([0-9]*\)")
GSWRITEBYTES2=$(expr "${GSFEEDBACK[1]}" : "[0-9]*[ ]*\([0-9]*\)")
GSLOGMEGS=$(expr "${GSFEEDBACK[3]}" : "[0-9]*[ ]*\([0-9]*\)")
if [ $GSREADBYTES1 -eq $GSREADBYTES2 -a $GSWRITEBYTES1 -eq
$GSWRITEBYTES2 ]
then
GSNOCALC="YES"
else
GSNOCALC="NO"
fi
print -p "select" \
" instance_name" \
" || chr(10) || host_name" \
" || chr(10) || '$(uname -a)'" \
" || chr(10) || 'Oracle'" \
" || chr(10) || version" \
" || chr(10) || 'New'" \
" || chr(10) || 'No'" \
" || chr(10) || 'No'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || " \
" (select round(sum(bytes)/1000000000)" \
" from (" \
" select bytes from dba_data_files" \
" union all" \
" select bytes from dba_temp_files" \
" )" \
" )" \
" || chr(10) || " \
" (select round(sum(bytes)/1000000)" \
" from v\$log" \
" )" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'No'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || 'N/A'" \
" || chr(10) || " \
" decode(" \
" '$GSNOCALC','YES',0," \
" round( ($GSREADBYTES2-$GSREADBYTES1) * 100" \
" / ( ($GSREADBYTES2-$GSREADBYTES1)" \
" + ($GSWRITEBYTES2-$GSWRITEBYTES1) ) ) )" \
" || chr(10) || 'N/A Random I/O (%)'" \
" || chr(10) || " \
" ($GSREADBYTES2-$GSREADBYTES1)/$GSWAIT/1048576" \
" || chr(10) || " \
" ($GSWRITEBYTES2-$GSWRITEBYTES1)/$GSWAIT/1048576" \
" || chr(10) || $GSLOGMEGS" \
" from v\$instance;"
FlushPipe
else
X=0
while [ $X -lt ${#GSFEEDBACK[*]} ]
do
echo "${GSFEEDBACK[$X]}"
(( X += 1 ))
done
fi
print -p "exit;"
wait
}
# Gather info from each running Oracle instance on this system export ORACLE_SID PATH
if [ -z "$1" ]
then
Usage "No hostname given (needed for file names)" 1 fi
ps -ef | grep ora_dbw0 | grep -v 'grep ora_dbw0' \ | awk '{print substr($NF,10)}' | while read ORACLE_SID do
PATH=/usr/lbin:/usr/bin:/usr/sbin:/softw/app/oracle/bin:/usr/local/bin
ORAENV_ASK=NO . oraenv
echo $ORACLE_HOME
GetStats $1 $ORACLE_SID &
done
wait
Received on Sat Jan 16 2010 - 16:01:50 CST