Job not running and db link failure

[mhknair]

have been provided with a synonym table. Physically the synonym table reside in far away location and is connected via a db link. I am inserting and updating some records in the synonym table frequently by a procedure. I run the procedure using Job scheduled every half an hour.

Now my problem is , whenever the physical connectivity to the remote location is affected due to physical link down or due to traffic my job get stopped and my job will not be running. Each and every time I need to recreate the job or force unbroken.

Is there any method to check the db link or any method to handle the exception? Since the oracle wait till timeout . I am using Oracle 9i.
Any solution is highly appreciated.

[PAVB]

I've used block below to test if DBLink connection is available - feel free to improve by writing to a log table.

Block assumes your DBLink name is "my_dblink".

DECLARE w_table_name VARCHAR2(36);
BEGIN
   ALTER SESSION CLOSE DATABASE LINK my_dblink;
   SELECT table_name
   INTO   w_table_name
   FROM   all_tables@my_dblink
   WHERE  rownum = 1;
EXCEPTION
   WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20999, 'my_dblink appears to be down');
END;