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?
Printable View
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?
Try GV$DBLINK instead of V$DBLINK.
V$DBLINK actually selects from GV$DBLINK with predicate "where inst_id = USERENV('Instance')".
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...
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....
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...
Yes, exactly. In my case the numbers were 185, 186 and 187.Quote:
Originally posted by marist89
My statistic#'s were 226, 227, 228. I'm assuming you meant the stats with names like '%dblink%'...