Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007

    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?

    Thanks in advance
    F. Fischer

  2. #2
    Join Date
    May 2004
    Dominican Republic
    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.

  3. #3
    Join Date
    Aug 2007
    Thanks for the answer.
    Actually the job started at 1:00 am and had not finished at 8:00 am. The network has been re-established at this time.

    But I will try your solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts