Re: Run command on sqlplus repetitively without reconnection

From: Philip Douglass <philipsd_at_gmail.com>
Date: Tue, 22 May 2007 10:42:31 -0400
Message-ID: <86de47840705220742n52ee9fb1y525c4c25a037f29c@mail.gmail.com>

Here's what I've been using for awhile now to accomplish the repetitive monitoring script in SQL*Plus pattern:

#!/bin/ksh

sqlplus -s / |& # Open a pipe to SQL*Plus

cat <& p &

print -p -- "exec dbms_application_info.set_client_info(client_info => '${USER}@${HOSTNAME}');"
print -p -- "exec dbms_application_info.set_module(module_name => '$(basename $0)', action_name => '$1');"

print -p -- "@$1"

while (true); do

        sleep $2
        print -p -- '/'

done

It doesn't get much shorter than that! I call it with two parameters: the SQL script to run, and the number of seconds to sleep between iterations. I do all the formatting in the SQL script. Here's my users.sql script that I keep running throughout the day on my terminal:

set feedback on
set linesize 110
set pagesize 10000
column username format a10
column osuser format a10
column sidserial format a9
column spid format a6
column machine format a10
column program format a19
column command format a6 heading CMD
column last_call_et format a11 heading LASTCALLET column row_wait_obj format a24

select   s.username,
         s.osuser,
         s.sid||','||s.serial# as sidserial,
         p.spid,
         substr(s.machine, instr(s.machine, '\')+1) as machine,
         nvl(s.program, p.program) as program,
         decode(a.name, 'UNKNOWN', null, initcap(a.name)) as command,
         cast(numtodsinterval(s.last_call_et, 'second') as interval day (1)
to second (0)) as last_call_et,
         (select lower(object_name) from dba_objects o where o.object_id =
s.row_wait_obj#) as row_wait_obj
from     v$process p, v$session s, audit_actions a
where    type != 'BACKGROUND'
and      s.username is not null
and      s.paddr = p.addr
and      a.action = s.command
and      s.status = 'ACTIVE'
and      ( s.module <> 'sqlmon.ksh'        or s.module is null )
and      ( s.action not like '%/users.sql' or s.action is null )

order by last_call_et desc
/

To stop the monitoring, I just break the process, with Ctrl-C -- I've never had a problem leaving background processes running. I actually use GNU Screen to keep a stack of various monitoring scripts in a single terminal window, and I just use Screen's exit command to end my session and kill all of the running scripts.

On 5/18/07, David Taft <oradbt054_at_gmail.com> wrote:
>
> Dimitre,
>
> This shell corprocess feature is very cool. Something new for my script
> toolbox. Thanks for posting it.
>
> I finally got around to playing with it on AIX 5.2 and found it works with
> the Bourne and Korn as well as ksh93 (Enhanced Korn). Below is a script I
> based on your posting. The trap I've used works, but gives the following
> output after doing a CTL-C.
>
> Error 45 initializing SQL*Plus
> Internal error
>
> The shell goes away as well as the Oracle session, but I am wondering if
> there is a more graceful way I should be using to shutdown the coprocess
> jobs. I couldn't figure it out from googling, nor from the AIX
> documentation. It is not a big deal, but any insight you or someone else
> may have would be welcomed.
>
> Thanks
>
> David Taft
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> #!/bin/sh
>
> # coproc_tst.sh
>
> # query co-process test.
> # Runs sqlplus query repeatedly in background without reconnection, but
> # displays output to screen.
> #
> # CTL-C to break (end).
> #
> # Intercept every signal relevent to the shell except 9 to exit.
> trap 'kill %2;exit' 1 2 3 15
>
> sqlplus -s "/ as sysdba"|&
>
> while :
> do
> print -p "set pagesize 999 linesize 120;"
> print -p "col status format a10"
> print -p "col EVENT format a30 word_wrapped"
> print -p "col sid format 9999"
> print -p "col NAME format a25"
> print -p "SELECT s.STATUS,w.EVENT,w.STATE,s.SID,w.WAIT_TIME,
> w.SECONDS_IN_WAIT \
> FROM v\$session_wait w,v\$session s \
> WHERE w.sid = s.sid AND s.username != 'SYS' \
> AND w.event != 'SQL*Net message from client' \
> ORDER BY s.status,w.event,w.state,s.sid;"
> sleep 5
> done &
>
> while :
> do
> read -p
> echo "$REPLY"
> done
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> From: "Radoulov, Dimitre"
>
> With ksh93 (and pdksh, on Linux for example) you can use co-processes
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 22 2007 - 09:42:31 CDT