How to trace back to a session on a remote database

Here is another snippet which will help a DBA managing an oracle database on windows in performing day-to-day maintenance activities.

Problem: Need to identify the sessions running on the remote database which are connected to the target database over a db link.

Let me explain with an example. Lets define two databases A and B. There is a session in database B which originates from database A. The objective is to find out the details of the session in the database A.


Solution:

Analysis of the session in consideration in database A will reveal something like this:

SID SERIAL# OSUSER PROCESS PROGRAM MACHINE
191 7483 NA\oracle-usa 4800:3336 ORACLE.EXE UEAP102



In the above output, the program oracle.exe is the database B. the osuser field "NA\oracle-usa" is the service account that is used to run the database B on machine UEAP102.

Follow the steps below to locate the details of the originating session on database B on UEAP102:

Login to database B and query the v$session and v$process:

select s.* from v$session s, v$process p where s.paddr=p.addr and p.spid='3336';

This gives you the details of the originating session in database B. If you notice, the spid to be used in B is the second portion of PROCESS column in database A, which in our case is 3336 (4800:3336)


You can use a tool like pslist or process explorer to see the thread id (this will not reveal much information, but just for your understanding.)

- Aravind Kamath