Re: managing open database links?

From: <stevedhoward_at_gmail.com>
Date: 18 Apr 2006 13:32:11 -0700
Message-ID: <1145392331.269555.65820@i39g2000cwa.googlegroups.com>

Here is another way...

localhost:oracle:localhost:/home/oracle>lsof -i | grep remotehost oracle 22216 oracle 13u IPv4 809174380 TCP localhost:56367->remotehost:2484 (ESTABLISHED) localhost:oracle:localhost:/home/oracle>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 18 16:27:29 2006

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning and Data Mining options

SQL> select sid,serial#,schemaname,osuser from v$session where paddr in(select addr from v$process where spid=22216);

       SID    SERIAL# SCHEMANAME                     OSUSER
---------- ---------- ------------------------------
------------------------------
       525        302 HOWARDS                        oracle

Elapsed: 00:00:00.04
SQL> This takes all outgoing connections to the remote host in which you are interested, gets each PID, and checks for that PID in v$process. Since that session is connected to the instance on the local host, you know that it is a database link connection to the remote host (it could be a JDBC connection from within the local instance well, I guess).

Regards,

Steve Received on Tue Apr 18 2006 - 15:32:11 CDT