Re: Korn Shell & Sqlplus

"Hand, Michael T" wrote:
>
> > Maybe one of you has some inspiration.
> >
> Environment: Compaq Tru64 5.1; Oracle 8.1.7.3
>
> > The concept is for a parent Korn shell to spawn a sqlplus child in
> > background (which will validate table structure via a PL/SQL Block) then
> > wait for a fixed time period before stopping/killing the sqlplus child.
> > After the sqlplus child is stopped some follow-up processing is needed to
> > "bookmark" where the sqlplus processing has stopped.
> >
> > My quick test script so far look like:
> > #!/bin/ksh
> > echo "Starting val_asc, spawning at " $(date) >> val_asc.out
> > sqlplus xxxxx/xxxx @val_asc3.pls >> val_asc3.log &
> valpid=$!
> > sleep 240
> > kill -KILL %1
> > echo "Leaving val_asc at " $(date) >> val_asc.out
> > exit
> >
> When the sqlplus line runs it starts 2 processes:
> > oragdv 169035 167185 0.0 10:00:20 pts/1 0:00.18 sqlplus
> > oragdv 169652 169035 0.0 10:00:22 ?? 0:00.13 oracleGDV
> > (DESCRIPTION=(LOCAL=YES). . .
> >
> The problem is when the kill command executes, only the first of these 2
> processes is killed immediately. The 2nd (with the PPID at 1) continues
> processing until the PL/SQL Block completes (including database updates)
> before it terminates.
>
> > Here's the question. Is there a straightforward way to terminate that 2nd
> > process before starting post processing?
> >
> > Any suggestions would be appreciated as I have been bashing my head
> > against the proverbial wall for several days now ;)
> I have used the $! parameter to id the sqlplus PID and have been fiddling
> with ps & awk to try and obtain the 2nd process's PID but have yet to be
> successful
>
> > Michael Hand
> Polaroid Corp
>

Michael,

  Killing Oracle server processes is definitely bad manners. Especially if you are using MTS. Even if you are not, the proper way to kill an Oracle process is to use ALTER SYSTEM KILL SESSION. I think that you should use the DBMS_APPLICATION_INFO package to set something like 'Hey! Shoot me!' in your CLIENT_INFO column in V$PROCESS - it would make it easier to another process to log as DBA, spot the process and bang.   Another thing that you might want to explore are profiles - you can define and set a profile with limits to suit your needs, and let Oracle do the dirty work.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).