CODEWITHSUNDEEP
oracle-database
Law
Law

Reputation: 31

Details of other databases accessing our Oracle database using DB links

Is there a way to find the details of the databases accessing our Oracle database using the DB links? Dba_db_links holds the information about the DB links that we have in our database to access other databases, but is there a similar kind of table from where we can find the DB links accessing our database or is that getting recorded some where?

Thanks in Advance.

Upvotes: 3

Views: 7441

Answers (3)

Mubasheer Ahmed
Mubasheer Ahmed

Reputation: 71

You might be looking out for this.

Step 1: Check the hash_value of the session in X database. select sql_hash_value from v$session where sid=&sid;

Step 2: Check the full SQL of the session in X database where the SQL is fired. select sql_fulltext from v$sql where hash_value=&hash_value;

Step 3: Make a note of all the DB links invloved in the SQL and identify the hosts for those DB links. select * from dba_db_links where db_link like upper('&db_link');

Step 4: In each host (say only one remote host, pointing to database Y) and database X itself, fire the above query (Tom Kyte's) to gather the session details of sessions coming from remote DBs.

Step 5: In database X, check the SID of interest and its corresponding GTXID. Look for the same GTXID in the remote host Y.

Step 6: Get the session ID from the database Y for this GTXID and check the session waits or other details.

Upvotes: 1

Mark J. Bobak
Mark J. Bobak

Reputation: 14433

I agree with Justin, that there's no way to determine an explicit list of all databases that have database links into a given database.

However, it is possible to monitor active database links. You can use the following query to see what sessions are via database links and from which databases:

-- who is querying via dblink?
-- Courtesy of Tom Kyte, via AskTom
-- this script can be used at both ends of the database link
-- to match up which session on the remote database started
-- the local transaction
-- the GTXID will match for those sessions
-- just run the script on both databases

Select /*+ ORDERED */
substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10)      "ORIGIN",
substr(g.K2GTITID_ORA,1,35) "GTXID",
substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" ,
s2.username,
substr(
   decode(bitand(ksuseidl,11),
      1,'ACTIVE',
      0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'),
      2,'SNIPED',
      3,'SNIPED',
      'KILLED'
   ),1,1
) "S",
substr(w.event,1,10) "WAITING"
from  x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w, v$session s2
where  g.K2GTDXCB =t.ktcxbxba
and   g.K2GTDSES=t.ktcxbses
and  s.addr=g.K2GTDSES
and  w.sid=s.indx
and s2.sid = w.sid;

Hope that helps.

Upvotes: 3

Justin Cave
Justin Cave

Reputation: 231801

When you create a database link in database A that points at database B, there is no notification sent to database B so there is no data dictionary table in B that will tell you that A has a link to it. As far as B is concerned, database A is simply another client that periodically opens a connection to the database.

Generally, when A wants to create a database link to B, a user will be created in B for this purpose (assuming the database link uses a fixed user rather than the current user) since you don't want the password for this account to expire regularly and you don't want the database link to be broken if a particular human leaves the company and has his or her accounts removed. You can audit connections on B, either for the particular accounts that have been created for database links or across all users, and then look through the audit logs to identify connections that are coming from servers that house other databases.

Upvotes: 1

Related Questions