Oracle-L: RE: How to Determine Oracle Session ID given UNIX PID?

From: Fermin Bernaus Berraondo <fbernaus_at_sammic.com>
Date: Mon, 07 Apr 2003 01:28:39 -0800
Message-ID: <F001.0057B975.20030407012839@fatcity.com>

I asked for the very same thing a couple of weeks ago, the message's subject was: "Which process is taking up so much CPU???" just in case you want to look for the worthy replies I got in the archives. The scripts were similar to this one. I can send the messages to you if you want to. -----Mensaje original-----
De: root_at_fatcity.com [mailto:root_at_fatcity.com]En nombre de Goulet, Dick Enviado el: domingo, 06 de abril de 2003 16:54 Para: Multiple recipients of list ORACLE-L Asunto: RE: How to Determine Oracle Session ID given UNIX PID?

Here's the script that I use to get the session & the current SQL statement:

  define sid = &1
  column client_info format A30 trunc
  column program format A40 trunc
  break on sql_address on sql_hash_value   column sql_address noprint new_value _addr   column sql_hash_value noprint new_value _hash   set verify off
  set linesize 132
  spool /ora1/pid.lis
  select spid, sid, v$session.status, client_info, v$session.program, sql_address, sql_hash_value   from v$session, v$process
  where paddr = addr
    and spid = '&sid'
    and v$session.username is not null;

  select sql_text
  from v$sqltext_with_newlines
  where address = '&_addr'
    and hash_value = &_hash
  order by piece;
  undef sid
  spool off
  exit

  Dick Goulet
    -----Original Message-----
    From: Sam Bootsma [mailto:sbootsma_at_gbrownc.on.ca]     Sent: Friday, April 04, 2003 6:19 PM     To: Multiple recipients of list ORACLE-L     Subject: How to Determine Oracle Session ID given UNIX PID?

Oracle 7.3.4 running on AIX 4.3.3

The ps command on our AIX box shows a UNIX process taking 50% of the CPU. The PID is 89510. When I try to find the corresponding session on the Oracle database it returns no rows. Is there a way I can map the given PID on UNIX to a session id on Oracle? See below for the query I ran.

SQL> l

1 select sid from v$session

2* where process like '%89510%'

SQL> / no rows selected

Thanks for your information!

Sam

sbootsma_at_gbrownc.on.ca

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Fermin Bernaus Berraondo
INET: fbernaus_at_sammic.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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). Received on Mon Apr 07 2003 - 04:28:39 CDT