RE: Identify PID for remote database handle--CLARIFICATION...

To the DBI/DBD list:

Let me rephrase this question a bit.=20
I have a perl program running on server A which makes a remote
connection to a database running on server B using the DBI/DBD construct
of a database handle/statement handle. On server A, the perl program has
a process ID, identified by $$ or $PID.=20

On server B where the actual DDL inside the statement handle is running,
resources are being consumed by the database to construct the query
return set. That resource usage is being accounted for somehow at the OS
level on server B. My assumption is that they are assigned a PID by the
OS.... It is this process id which I would like to capture.

I hope this clarifies what I am looking for ...

Thanks,
catharine




-----Original Message-----
From: Chris Sarnowski [mailto:csarnows@pcbi.upenn.edu]=20
Sent: Tuesday, May 23, 2006 7:04 AM
To: dbi-users@perl.org
Cc: Drozdowski, Catharine
Subject: Re: Identify PID for remote database handle

I don't know of any way to do this using DBI or DBD::Oracle.
You might get better answers on an Oracle DBA list (try
http://www.freelists.org/list/oracle-l or http://www.lazydba.com )

This seems to work in Oracle 9.2, but I just hacked it together in a
couple of minutes and it's not thoroughly tested (i.e. testcases =3D 1
with 100% success). =20
Also, there are probably
better ways to find your own SID. This of course assumes that the user
has access to the V$ tables (which they probably shouldn't have).

select vp.spid from v$process vp, v$session vs where vs.sid =3D (select
sid from v$mystat where rownum =3D 1) and vp.addr =3D vs.paddr;


I've been thinking about why you might want this and now I'm not sure
it's the answer you want anyway. Running sqlplus inside of perl seems
redundant for most purposes.
And now that I think about it I'm sure DBI/DBD::Oracle can't help you
here because it is not going to know anything about the sqlplus
connection, just the DBI handle.
So it really is off-topic for this list.

-Chris

On May 22, 2006, at 6:07 PM, Drozdowski, Catharine wrote:
> How can I find the server pid for a sqlplus session which is logged on

> remotely inside a perl program... I can find the local PID using $$ or

> $PID, but how can I find the pid for the statement/database handle=20
> which is actually logged onto the database doing the work...
>
> Env: Oracle, Solaris
>
>
> Catharine Drozdowski
> Mentor Graphics Corporation
> IT Systems Services
> 1 503 685 7906
>
>
>
>