Skip to Main Content
  • Questions

  • Database link : Relation of sessions between databases

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sébastien.

Asked: August 30, 2021 - 2:09 pm UTC

Last updated: October 11, 2021 - 10:52 am UTC

Version: 11g 12c 19c

Viewed 1000+ times

You Asked

Hello,

In the context of database link, is there a possibility to get the relation between both sessions of database "A" and database "B"? (database "A" query objects from database "B" using a dblink)
In other words, from database "B" can we retrieve the SID of database "A" that create the session in database "B" (and vis versa, from database "A" the SID of database "B" that is created for the dblink)?

I hope that my question is understandable.

Thank you very much,
Sébastien.

and Connor said...

From the perspective of the receiving database, the request is just like another client. You might be able to glean from information from the session details.

eg A normal remote connection coming to my database with SQLPlus

SQL> select * from v$session where sid = 124
  2  @pr
==============================
SADDR                         : 00007FFC20C1CA88
SID                           : 124
SERIAL#                       : 52150
AUDSID                        : 30290777
PADDR                         : 00007FFC205B2C68
USER#                         : 107
OSUSER                        : connor
PROCESS                       : 3424:1308
MACHINE                       : XPS13
PORT                          : 62660
TERMINAL                      : XPS13
PROGRAM                       : sqlplus.exe
TYPE                          : USER


and here is a connection coming via a database link (from the database running on the same remote machine as above)

SQL> select * from v$session where sid = 497
  2  @pr
==============================
SADDR                         : 00007FFC1F2117F0
SID                           : 497
SERIAL#                       : 33833
AUDSID                        : 30290782
PADDR                         : 00007FFC20618950
USER#                         : 107
OSUSER                        : OracleServiceDB19
PROCESS                       : 4724:11880
MACHINE                       : XPS13
PORT                          : 62725
TERMINAL                      : XPS13
PROGRAM                       : ORACLE.EXE
TYPE                          : USER
MODULE                        : ORACLE.EXE


Those values will vary by platform (the ones above are Windows) but you get the idea.

Also some (unsupported) options here

https://stackoverflow.com/questions/11991759/details-of-other-databases-accessing-our-oracle-database-using-db-links/11999137#11999137

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Hi

Sébastien Tromme, October 11, 2021 - 7:35 am UTC

Thank you for your feedback.
Regards.
Connor McDonald
October 11, 2021 - 10:52 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database