Insert Into Remote Table Using DBLINK Over VPN Tunnel Hangs on Large Number of Rows
Last updated on AUGUST 31, 2016
Applies to:Oracle Net Services - Version 126.96.36.199.0 to 188.8.131.52.0 [Release 9.2 to 11.1]
Information in this document applies to any platform.
***Checked for relevance on 30-JAN-2013***
***Checked for relevance on 07-AUG-2014***
Suddenly an INSERT (as) SELECT FROM DBLINK hangs with source database waiting on "SQL*Net more data from dblink" wait event and the remote (target) shadow process disappearing.
WEB_SUMM WHERE ROWNUM < 281);
280 rows created.
SQL> INSERT INTO TABLEX (SELECT SESSION_ID, OBS_DT, UNIT_ID FROM
WEB_SUMM WHERE ROWNUM < 282);
This hangs indefinitely with source in "SQL*Net more data from dblink" wait event.
Similarly, create table as select over the DBLINK also hangs:
WEB_SUMM WHERE ROWNUM < 32767);
SQL> CREATE TABLE TEST1 AS (SELECT SESSION_ID, OBS_DT, UNIT_ID FROM
WEB_SUMM WHERE ROWNUM < 32768);
-- This also hangs indefinitely with source in "SQL*Net more data from dblink" wait event.
A PL/SQL doing an explicit cursor just fetching (not inserting) fails after 20 or so rows.
The only thing that runs to completion is plain SELECT from Sqlplus.
Database Links to databases on Local Area Network (LAN) do not exhibit this problem. This issue is limited to a database link where the target is a remote database accessed via a VPN Tunnel using default port 1521.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms