ORA-00036 maximum number of recursive SQL levels (string) exceeded

From: EdStevens <quetico_man_at_yahoo.com>
Date: 9 May 2006 09:08:21 -0700
Message-ID: <1147190901.072706.83040@y43g2000cwc.googlegroups.com>

Platform: Solaris 9, Oracle EE 8.1.7.4, Oracle EE 10.2.0.1

Two databases on the same box, call them eps8pd (8.1.7.4) and epspd (10.2.0.1)

In eps8pd, I created a db link pointing to epspd:

CREATE PUBLIC DATABASE LINK "LNK_EPSPD" CONNECT TO "NOBELEPS"     IDENTIFIED BY "********"
    USING 'EPSPD' then, connected to eps8pd as NOBELEPS (same user exists on both db's):

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production With the Partitioning option
JServer Release 8.1.7.4.0 - Production

SQL> select count(*) from user_tables_at_lnk_epspd; select count(*) from user_tables_at_lnk_epspd

                                 *

ERROR at line 1:

ORA-02068: following severe error from LNK_EPSPD
ORA-00603: ORACLE server session terminated by fatal error
ORA-00604: error occurred at recursive SQL level 52
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-00604: error occurred at recursive SQL level 52
ORA-00036: maximum number of recursive SQL levels (50) exceeded

Searching the ng archives, MetaLink, and Google on ORA-00036 gives a few hits, all talking about the likelyhood of a recursive trigger. But as you can see, my example is a simple select that shouldn't generate much recursive SQL. Linking FROM the 10.2 db TO the 8.1 db causes no problems. I realize that this may be an un-resolvable problem with 8
-> 10 connections, but am making one last attempt to find a possible
work-around before going to plan 'D'.

Thanks. Received on Tue May 09 2006 - 11:08:21 CDT