RE: How to derive the process ID of its own session?

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Fri, 22 Feb 2008 09:32:10 -0500
Message-ID: <D1DC33E67722D54A93F05F702C99E2A901FE0CF4@usahm208.amer.corp.eds.com>

 

Note if the complete query that errored with ORA-01427 was something like

select paddr from v$session
where sid = (select sid from v$mystat where rownum = 1)

It can be fixed by adding
and rownum =1
as the third line

Normally, if dedicated sessions are used, only one session will exist for a specific process however Oracle will use a second session to run recursive SQL so you can sometimes get two sessions for a background process.

• Mark D Powell -- Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Peter Teoh Sent: Friday, February 22, 2008 4:31 AM
To: Ghassan Salem
Cc: oracle-l_at_freelists.org
Subject: Re: How to derive the process ID of its own session?

Thank you very much, it worked!!

Just FYI, I tried this way, it worked on 10gR2 (Linux), but gave error on 9iR2 (HP-UX):

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> @getspid1

                   (select paddr from v$session
                    *

ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

SQL> !cat getspid1.sql

select spid from v$process
where addr=

                   (select paddr from v$session
                    where audsid = userenv( 'sessionid' )) ;

I suspect most probably a bug which I did not patch. Just to share, don't have to reply :-).

On Fri, Feb 22, 2008 at 4:55 PM, Ghassan Salem <salem.ghassan_at_gmail.com> wrote:
> you can get your sid (at least from v$mystat) and the join v$session
> and v$process using the sid, paddr and addr:
> select p.spid
> from v$session s, v$process p
> where s.paddr=p.addr
> and sid in (select sid
> from v$mystat
> where rownum=1);
>
>
>
> On Fri, Feb 22, 2008 at 6:40 AM, Peter Teoh <htmldeveloper_at_gmail.com>
wrote:
> >
> >
> >
> > Requirements:
> >
> > How to derive the OS process ID of its OWN Oracle session?
> > Assumption is that we have access to V$PROCESS.
> >
> > In 10g, I solved it this way:
> >
> > From userenv('PID') this get mapped to the PID field of V$PROCESS.
> > So I just derived the OS process ID (which is in the V$PROCESS.SPID
> > field) by searching through V$Process for the corresponding
> > USERENV('PID').
> >
> > But in 9iR2, userenv('PID') is not a valid parameter. So what is
the
> > equivalent parameter to be passed to userenv()?
> >
> > Thank you very much.
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>

--
Peter Teoh
HP: 96809281
--
http://www.freelists.org/webpage/oracle-l


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

Received on Fri Feb 22 2008 - 08:32:10 CST