Which DB Links are open?

[marist89]

I want to find out which DB links are curently open by any user. I looked into v$dblink, but that only reports links that are open from the current session. Any ideas?

[jmodic]

Try GV$DBLINK instead of V$DBLINK.

V$DBLINK actually selects from GV$DBLINK with predicate "where inst_id = USERENV('Instance')".

[marist89]

No, I don't get any rows from there either when I know I have a db_link open. Does GV$DBLINK only report on ACTIVE links, or can a link be open but have no activity going over it...

[jmodic]

Sorry, Jeff, my previous suggestion was totaly out of place. GV$ and V$ views return the same values on non-OPS databases (where inst_id = USERENV('Instance'))! They are the same, as there is only one database instance per database. No wonder you recieved the same results from both V$DBLINK and GV$DBLINK.

I don't realy know of any way to find out which database links are used and by which session. Once a session establishes a connection to the remote database over DB_link, that link remains open for the duration of that session or until that session isues "ALTER SESSION CLOSE DATABASE LINK linkname;". So if you assume that db_links are not being closed explicitely, you can get information of *which sessions* have database link opened by querying V$SESSTAT where values of db_link related statistics (statistics# 185, 186 and 187 are related to db_link activity in 8.1.7) are > 0. But again, this will only tell which sessions have database link connections opened, not which database links are in use. I guess this is not what you want. It seems that you are only able to determine the opened db_links for your own session.

If you find of any way to answer your question, please post it here....

[marist89]

My statistic#'s were 226, 227, 228. I'm assuming you meant the stats with names like '%dblink%'...

That at least lets me know who is using some db link, but not necessarily which db link they are using. Still poking...

[jmodic]

Originally posted by marist89
My statistic#'s were 226, 227, 228. I'm assuming you meant the stats with names like '%dblink%'...

Yes, exactly. In my case the numbers were 185, 186 and 187.