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?
Best Answer
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) LOOPDBMS_SESSION.CLOSE_DATABASE_LINK(aLink.DB_LINK);END LOOP;or
DECLAREDATABASE_LINK_IS_NOT_OPEN EXCEPTION;PRAGMA EXCEPTION_INIT(DATABASE_LINK_IS_NOT_OPEN, -2081);BEGINDBMS_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.