Skip to Main Content
  • Questions

  • List out external databases using DB link

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Venkat.

Asked: March 07, 2017 - 1:28 pm UTC

Last updated: March 09, 2017 - 4:40 am UTC

Version: 10g

Viewed 1000+ times

You Asked

Hello Gurus,

Have basic knowledge in Oracle DB Admin or Profiling. I am unable to profiling for below requirement.

"Want to identify the list of other database which are usign my Database by created as DB Link."
Suppose my company have 10 database as "db01, db02, ..., db10". The database "db01" has been created as dblink to dbNN databases. How do I find who are using my parent database "db01". I do not have SYS permissions to "db02 to db10" but I can login "db01" and fetch SQLs.

Appreciate your help in advance.

Thanks,
Venkat

and Connor said...

You could check the session details of connected sessions. Here's an example of a session coming in through a db link on Windows database

SQL> select * from v$session where sid = 259
  2  @pr
==============================
SADDR                         : 000000007911ACE0
SID                           : 259
SERIAL#                       : 48253
AUDSID                        : 70014
PADDR                         : 000000007AAD1428
USER#                         : 107
USERNAME                      : MCDONAC
...
OSUSER                        : SYSTEM
MACHINE                       : server
PROGRAM                       : ORACLE.EXE
TYPE                          : USER
...


You could use the os user/machine/program as a guide. You can issue the AUDIT SESSION command to track these details so you can look for them later, or a AFTER LOGON trigger to catch more details if needed - plenty of stuff you can catch via the SYS_CONTEXT function for the incoming session.

We're not taking comments currently, so please try again later if you want to add a comment.
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library