Re: Run command on sqlplus repetitively without reconnection
- From: "Philip Douglass" <philipsd@xxxxxxxxx>
- To: oradbt054@xxxxxxxxx
- Date: Tue, 22 May 2007 10:42:31 -0400
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@xxxxxxxxx> 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
Other related posts:
