Alter public database link to private in oracle

Metalink 207303.1 is the Client/ Server Interoperability Matrix which will be the definitive documentation. Since the express edition of 10g is based on Oracle 10.2, a database link to an 8.1.7 database should be supported. Database links to earlier versions of 8i (8.1.5 or 8.1.6) are not supported from a 10.2 database. For general sanity, I'd strongly suggest that the 8i side be running at least the 8.1.7.4 patchset but that isn't strictly required.

Step by step:

1. There is no way to test a dblink other than select a dummy over it
2. A network timeout affects only the running transaction that is currently the dblink. There is no recorded state about the dblink, there is no status like ACTIVE or BROKEN. DBLink creation simply instruct the DB about remote host, port, tns, user and password. Hence drop and recreate the dblink does not affect or change the network or ODBC problem. A pratical case is: select sysdate from dual@dblink. A firewall is in place between both databases. Source DB opens the connection, retrieve data and leave connection opened. After 15mins the firewall drop the connection. At minute 20 another user performs the same query. The database will reopen the connection.
3. The best automatic way should be to query a table; this only if we are experiecing a simple network issue.

Apart from this I understand that you get the connection dropped and you have to way to re-establish it without drop and recreate the database. Metalink (formerly My Oracle Support) suggest to check if HS=OK is correctly set in the tnsnames.ora. Attention: the HS=OK argument needs to be outside of the CONNECT_DATA portion of the entry. Here an example:

MSSQL =
(DESCRIPTION =
      (ADDRESS_LIST =
          (ADDRESS =
              (PROTOCOL = tcp)
              (HOST = 192.168.36.18)
              (PORT = 1522)
             )
        )
   (CONNECT_DATA =
        (SID = MSSQL)
   )
   (HS=OK)
)

Moreover I suggest you to check (and eventually post) logs on both sides. You can also increase the level of HS_FDS_TRACE_LEVEL parameter. In addition MOS reports some issue with DG4ODBC and suggest to debug them (and fill a bug if necessary) using trace on the ODBC layer.