3 Replies Latest reply: May 17, 2013 5:53 PM by Jason_S RSS

    Create View containing the status of DBLinks

    Jason_S
      I would like to create a View that contains the status of the current users DBLinks. This View would then be accessed by an BI platform to display this information to the end users.

      I know how to test an individual DBLink like this:
      select sysdate from dual@CHAP.WORLD;
      I know how to see the list of DBLinks from the user_ tables like this:
      select db_link from user_db_links;
      I have seen others use PLSQL and a cursor to test the links here: ([PLSQL Example|https://forums.oracle.com/forums/thread.jspa?messageID=2587498&#2587498] )

      What I want however is a view, not a procedure. I've also noticed that it take quite a while for the timeout to occur, and was wondering if there was a way to simply 'time' each query to determine if it was valid or not. For example, query the dblink and if it was less than 1 second return a "pass." I know that seems crude.

      Insert laugh track here for this attempt :) Even I laughed when I typed it. Maybe I should have cried.
      select 'select db_link from user_db_links where rownum = 1' as DBLink, sysdate from dual@||'select db_link from user_db_links where rownum = 1';
      Jason
      Oracle 10g