Re: SQLPLUS Question

From: Gerard H. Pille <ghp_at_skynet.be>
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

Original text of this message