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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Peter Teoh Sent: Friday, February 22, 2008 4:31 AM To: Ghassan Salem Cc: oracle-l@xxxxxxxxxxxxx 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@xxxxxxxxx> 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@xxxxxxxxx> 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. > > -- > > //www.freelists.org/webpage/oracle-l > > > > > > > > -- Peter Teoh HP: 96809281 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l