Insert Into Remote Table Using DBLINK Over VPN Tunnel Hangs on Large Number of Rows (Doc ID 742535.1)

Last updated on AUGUST 31, 2016

Applies to:

Oracle Net Services - Version 9.2.0.1.0 to 11.1.0.7.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***

Symptoms

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.

SQL> INSERT INTO TABLEX (SELECT SESSION_ID, OBS_DT, UNIT_ID FROM
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:

SQL> CREATE TABLE TEST1 AS (SELECT SESSION_ID, OBS_DT, UNIT_ID FROM
WEB_SUMM WHERE ROWNUM < 32767);

Table created.
---------------

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.

Changes

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.

Cause

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