Unanswered: Catching connection loss while job execution
I've the following problem:
A daily job starts 1:00 am. It calls a stored procedure in a package, which inserts like 500.000 rows into another database via a database link:
insert into <tablename>@<dblink> (....) select ... from <tablename>;
This works fine but one day the network had problems or something like that. The connection interrupted while the insert was running and there came no feedback, no error. So the job was waiting and waiting and waiting... and didn't run the next day because Oracle realized that the job was still running.
Is there any possibility to catch failures like this, to finish the job?
If the network was re-established faster enough, you would have received an ORA-02068. However, just until after you re-establish the connection you would have receive it, meaning that if it lasted a few couple of hours you would wait just for that.
You can use the parameter SQLNET.RECV_TIMEOUT in sqlnet.ora on the machine that is RECEIVING (that is, the one on where you're executing the insert) the data to "force" for a timeout if not data is received within the interval you specify. After this interval, you will receive either a ORA-02068 and ORA-03113 and sqlnet.log would log a TNS-12535 specifying that your operation timed out.