ORA-03150: end-of-file on communication channel for database link

Question

In an Oracle database there's a big PL/SQL procedure being executed periodically that copies data from one DB to another one through a database link and it is failing after some hours with the following error:

ORA-03150: end-of-file on communication channel for database link 
ORA-02063: preceding line from DBPREMOTE 
ORA-06512: at "DBLOCAL.JOB_NAME", line 710 
...
ORA-06512: at line 1 

Line 710 is the first line of a procedure:

 execute immediate 'set constraints all deferred';

Then the procedure does some inserts and updates, which I guess are failing at some point due to PK, data not valid or whatever other reason. I guess that the exception is being pointing at that line because it is the first one, not because it is actually failing there, but I don't know for sure the real exception.

Is there any chance I can get the real exception so I can handle it?

Answer 1

A potential workaround could be to close the DB-Link after each usage with a PL/SQL procedure like this:

FOR aLink IN (SELECT * FROM V$DBLINK) LOOP
    DBMS_SESSION.CLOSE_DATABASE_LINK(aLink.DB_LINK);
END LOOP;

or

DECLARE
    DATABASE_LINK_IS_NOT_OPEN EXCEPTION;
    PRAGMA EXCEPTION_INIT(DATABASE_LINK_IS_NOT_OPEN, -2081);
BEGIN
    DBMS_SESSION.CLOSE_DATABASE_LINK('DBPREMOTE ');
EXCEPTION 
    WHEN DATABASE_LINK_IS_NOT_OPEN THEN 
        NULL;
END;

If the connections are dropped anyway, you should talk to your network guys. Perhaps they drop the connection by firewall settings. However, there could be many others reasons.