PL/SQL Dev query session lost on large resultsets after db update
vhbtechAug 21 2008 — edited Oct 29 2008We have a problem with our PL/SQL Developer tool (www.allroundautomations.nl) since updating our Database.
So far we had Oracle DB 10.1.0.5 Patch 2 on W2k3 and XP-Clients using Instant Client 10.1.0.5 and PL/SQL Developer 5.16 or 6.05 to query your DB. This scenario worked well.
Now we upgraded to ORACLE 10G 10.1.0.5 PATCH 25 and now our PL/SQL Developer 5.16 or 6.05 (on IC 10.1.0.5) can logon the db and also query small tables. But as soon as the resultset reaches a certain size, the query on a table won't come to an end and is always showing "Executing...". We can only press "BREAK" what the results in a "ORA-12152: TNS: unable to send break message" and "ORA-03114: not connected to ORACLE".
If i narrow the resultset down on the same table it works like before.
If i watch the sessions on small resultset-queries, i see the corresponding session, but on large resultset-queries the session seem to close immediately.
To solve this issue a already tried to install the newest PL/SQL developer 7.1.5(trail) or/and installing a newer instant client version (10.2.0.4), which both did not solve the problem.
Is there a new option in 10.1.0.5 Patch 25 (or before) which closes sessions if the resultsets getting to large over a slower internet connection?
btw. using sqlplus in the instantclient directory or even excel over odbc on the same client, returns the full resultset without problems. Could this be some kind of timeout problem ?
Edit:
Here is a snippet of the tracefile on the client right after Executing the select-statement. Some data seems to be retrieved and than it ends with these lines:
(1992) [20-AUG-2008 17:13:00:953] nsprecv: 2D 20 49 6E 74 72 61 6E |-.Intran|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: 65 74 2D 47 72 75 6E 64 |et-Grund|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: 6C 61 67 65 6E 02 C1 04 |lagen...|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: 02 C1 03 02 C1 0B 02 C1 |........|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: 51 00 02 C1 03 02 C1 2D |Q......-|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: 05 48 4B 4F 50 50 01 80 |.HKOPP..|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: 03 3E 64 66 01 80 07 78 |.>df...x|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: 65 0B 0F 01 01 01 07 76 |e......v|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: C7 01 01 09 01 01 07 76 |.......v|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: C7 01 01 18 01 01 07 78 |.......x|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: 65 0B 0F 01 01 01 07 76 |e......v|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: C7 01 01 09 01 01 07 76 |.......v|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: C7 01 01 18 01 01 02 C1 |........|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: 3B 02 C1 02 01 80 00 00 |;.......|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: 00 00 00 00 00 00 00 00 |........|
(1992) [20-AUG-2008 17:13:00:953] nsprecv: 00 00 01 80 15 0C 00 |....... |
(1992) [20-AUG-2008 17:13:00:953] nsprecv: normal exit
(1992) [20-AUG-2008 17:13:00:953] nsrdr: got NSPTDA packet
(1992) [20-AUG-2008 17:13:00:953] nsrdr: NSPTDA flags: 0x0
(1992) [20-AUG-2008 17:13:00:953] nsrdr: normal exit
(1992) [20-AUG-2008 17:13:00:953] snsbitts_ts: entry
(1992) [20-AUG-2008 17:13:00:953] snsbitts_ts: acquired the bit
(1992) [20-AUG-2008 17:13:00:953] snsbitts_ts: normal exit
(1992) [20-AUG-2008 17:13:00:953] snsbitcl_ts: entry
(1992) [20-AUG-2008 17:13:00:953] snsbitcl_ts: normal exit
(1992) [20-AUG-2008 17:13:00:953] nsdo: what=1, bl=2001
(1992) [20-AUG-2008 17:13:00:953] snsbitts_ts: entry
(1992) [20-AUG-2008 17:13:00:953] snsbitts_ts: acquired the bit
(1992) [20-AUG-2008 17:13:00:953] snsbitts_ts: normal exit
(1992) [20-AUG-2008 17:13:00:953] nsdo: nsctxrnk=0
(1992) [20-AUG-2008 17:13:00:953] snsbitcl_ts: entry
(1992) [20-AUG-2008 17:13:00:953] snsbitcl_ts: normal exit
(1992) [20-AUG-2008 17:13:00:953] nsdo: normal exit
(1992) [20-AUG-2008 17:13:00:953] nioqrc: exit
(1992) [20-AUG-2008 17:13:00:953] nioqrc: entry
(1992) [20-AUG-2008 17:13:00:953] nsdo: entry
(1992) [20-AUG-2008 17:13:00:953] nsdo: cid=0, opcode=85, bl=0, what=0, uflgs=0x0, cflgs=0x3
(1992) [20-AUG-2008 17:13:00:953] snsbitts_ts: entry
(1992) [20-AUG-2008 17:13:00:953] snsbitts_ts: acquired the bit
(1992) [20-AUG-2008 17:13:00:953] snsbitts_ts: normal exit
(1992) [20-AUG-2008 17:13:00:953] nsdo: rank=64, nsctxrnk=0
(1992) [20-AUG-2008 17:13:00:953] snsbitcl_ts: entry
(1992) [20-AUG-2008 17:13:00:953] snsbitcl_ts: normal exit
(1992) [20-AUG-2008 17:13:00:953] nsdo: nsctx: state=8, flg=0x100400d, mvd=0
(1992) [20-AUG-2008 17:13:00:953] nsdo: gtn=127, gtc=127, ptn=10, ptc=2011
(1992) [20-AUG-2008 17:13:00:953] snsbitts_ts: entry
(1992) [20-AUG-2008 17:13:00:953] snsbitts_ts: acquired the bit
(1992) [20-AUG-2008 17:13:00:953] snsbitts_ts: normal exit
(1992) [20-AUG-2008 17:13:00:953] snsbitcl_ts: entry
(1992) [20-AUG-2008 17:13:00:953] snsbitcl_ts: normal exit
(1992) [20-AUG-2008 17:13:00:953] nsdo: switching to application buffer
(1992) [20-AUG-2008 17:13:00:953] nsrdr: entry
(1992) [20-AUG-2008 17:13:00:953] nsrdr: recving a packet
(1992) [20-AUG-2008 17:13:00:953] nsprecv: entry
(1992) [20-AUG-2008 17:13:00:953] nsprecv: reading from transport...
(1992) [20-AUG-2008 17:13:00:968] nttrd: entry
Message was edited by:
vhbtech