DBLink hangs

From: Sara Koike <sara.k.koike_at_accenture.com>
Date: 7 Nov 2002 20:15:45 -0800
Message-ID: <d52e54dc.0211072015.4974a776@posting.google.com>

We're running Oracle 8.1.7.3 on Advanced Windows Server 2000. We have a stored procedure that executes a SQL statement that opens a db link retrieves data from the remote database then inserts it into the local database.

On occasion, the link seems to hang. On the local database, the session is active, but there is no I/O. On the remote database, the session does not exist. In addition, on occasion there is a lock on the index of the table the proc is inserting into.

We haven't been able to replicate this issue in our dev or test environments. We think it looks like some type of network issue that is not handled by the Oracle.

We may have a workaround. A Visual Basic program calls the proc using ODBC. We have enabled query timeout, so now after X minutes after execution of the proc, the ODBC connection should error out, which I hope will release the session.

Has anyone seen this? I've heard that 8.1.7.4.5 will fix this problem, but I'd like to know what other's thoughts are. Received on Thu Nov 07 2002 - 22:15:45 CST