From: Gerard H. Pille on 16 Jan 2010 17:01 Jeremy schreef: > In article<4b5212b2$0$2864$ba620e4c(a)news.skynet.be>, ghp(a)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
From: Gerard H. Pille on 16 Jan 2010 17:04 Jeremy schreef: > In article<hit6ej$phc$4(a)solani.org>, gogala.mladen(a)gmail.com says...> >> On Sat, 16 Jan 2010 18:37:24 +0000, Jeremy wrote: >> >> >>> 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? >> >> Yes. It's called PL/SQL and is available as of the version 6. > > Is that intended to be serious answer? > > Ah, sorry to have underestimated you, Jeremy, but you must be brighter than you look, to see through him.
From: Galen Boyer on 16 Jan 2010 17:03 Mladen Gogala <gogala.mladen(a)gmail.com> writes: > On Sat, 16 Jan 2010 22:39:24 +0100, Gerard H. Pille wrote: > > >> Please, Mladen Gogala, why don't you shut up about things you know >> nothing about? > > Because I've written many scripts using ksh, bash and DCL as well Perl > and PHP and because I actually know a bit about Oracle. What Mladen stated is true. -- Galen Boyer --- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: Gerard H. Pille on 16 Jan 2010 17:08 Mladen Gogala schreef: > On Sat, 16 Jan 2010 22:39:24 +0100, Gerard H. Pille wrote: > > >> Please, Mladen Gogala, why don't you shut up about things you know >> nothing about? > > Because I've written many scripts using ksh, bash and DCL as well Perl > and PHP and because I actually know a bit about Oracle. > > > Everyone knows you know a bit about it (Oracle). You know near to nothing about ksh.
From: Gerard H. Pille on 16 Jan 2010 17:10
Gerard H. Pille schreef: > Jeremy schreef: >> In article<hit6ej$phc$4(a)solani.org>, gogala.mladen(a)gmail.com says...> >>> On Sat, 16 Jan 2010 18:37:24 +0000, Jeremy wrote: >>> >>> >>>> 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? >>> >>> Yes. It's called PL/SQL and is available as of the version 6. >> >> Is that intended to be serious answer? >> >> > > Ah, sorry to have underestimated you, Jeremy, but you must be brighter > than you look, to see through him. I have to correct myself, Mladen is right that you can use PL/SQL inside an sqlplus script. It could very well be that that is enough to solve your problem. |