Re: Unix program to return connected process ids

From: Jonathan Lewis <Jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 6 Jan 1995 20:29:10 +0000
Message-ID: <789424150snz_at_jlcomp.demon.co.uk>

In article <1994Dec30.030825.26166_at_mother.bates.edu>

           rspell_at_bates.edu "Rob Spellman" writes:

: I'm looking for a program which will print out a list of the unix
: process ids which are connected to an Oracle database. My idea is to
: use this program to disconnect process from Oracle before shutting down
: the database with an dbshut.
:

SQLP*Plus:

Join v$process to v$session where:

        v$session.paddr = v$process.addr.
Look at columns like:

	v$session.process
	v$session.program
	v$process.spid
	v$process.program

These will tell you the Process ids of foreground sessions, the program name where possible, the process ids of the shadows, and the shadow/server name. They an also help you identify processes coming from alien machines (particularly PCs).

I can't be more specific as there has been migration of information across columns in the different versions of 6 and 7.

NB: a cute trick on Unix systems once you have the script written: Ignore db_shut, write your own shell script. Use SQL*DBA to run the script but use two sessions at once:

    SQL*DBA #1 goes into background, sleeps 10 seconds, then     runs the script to produce the list then exits.

    SQL*DBA #2 starts up immediately after the first one

		(i.e.
			sqldba... &
			sqldba ...
		)

    sleeps for 5 seconds (to make sure that #1 has connected) then     issues a shutdown normal.

Effect:

    #1 runs the script only after the database IS shutting down, so     no more processes will be able to connect, so the report it     produces is the definitive list of processes to kill.

The rest of the sript then handles the gradual, and increasingly violent, killing of the processes that are holding #2 open.

Using this method, you can ALMOST guarantee a normal shutdown of the database with minimum abuse of power.

-- 
Jonathan Lewis

Received on Fri Jan 06 1995 - 21:29:10 CET