Which DB Links are open?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Which DB Links are open?

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Question


    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?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Try GV$DBLINK instead of V$DBLINK.

    V$DBLINK actually selects from GV$DBLINK with predicate "where inst_id = USERENV('Instance')".
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width