Obtain available Database Links.

DECLARE

  BAD_USERNAME EXCEPTION; PRAGMA EXCEPTION_INIT(BAD_USERNAME, -1017);  
  BAD_TNS EXCEPTION; PRAGMA EXCEPTION_INIT(BAD_TNS, -12154);  
    
  CURSOR CUR_DBLINK IS SELECT DB_LINK FROM ALL_DB_LINKS ORDER BY DB_LINK;
      
  V_TOTAL_DBL_AVAIL PLS_INTEGER := 0;
  V_TOTAL_DBL       PLS_INTEGER := 0;
 
BEGIN

  DBMS_OUTPUT.PUT_LINE('DATABASE LINKS AVAILABLE');
  DBMS_OUTPUT.PUT_LINE('************************');
    
  FOR I IN CUR_DBLINK LOOP                   

     BEGIN               
       
        EXECUTE IMMEDIATE ('SELECT 1 FROM DUAL@'||I.DB_LINK);
        DBMS_OUTPUT.PUT_LINE(I.DB_LINK);
        V_TOTAL_DBL_AVAIL := V_TOTAL_DBL_AVAIL + 1;        
     EXCEPTION
        WHEN BAD_TNS OR BAD_USERNAME THEN NULL;
        WHEN OTHERS THEN NULL;
       
     END;
 
     V_TOTAL_DBL := NVL(CUR_DBLINK%ROWCOUNT, 0);
                         
  END LOOP;
                                             
  DBMS_OUTPUT.PUT_LINE('');  
  DBMS_OUTPUT.PUT_LINE('Total:'||V_TOTAL_DBL_AVAIL||'/'||V_TOTAL_DBL);
 
END;
/