"select" query to remote database fails to retrieve data.

I am facing a strange issue in oracle 11g. It may not be a database issue, can be related to network also.

In brief, Not able to execute sql statements from my workstation (oracle 11g client) which returns more amount of data for ex: select * from v$session,select * from dict; I can execute simple statements like select * from version / some user tables with less number of data by limiting with �where� clause.
This is not an issue when I am in a remote workstation which is located in the same network as the database server (in a remote location). Issue is only from local workstations/applicationserver alone.

Enviornment is ,

Server � Oracle 11.2.0.1.0 64bit on RHEL 5.4 Client - Oracle 11.1.0.6.0 32 bit on Windows Xp.

C:\Documents and Settings>sqlplus system/******_at_XXXX

SQL*Plus: Release 11.1.0.6.0 - Production on Wed May 12 11:11:39 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL>
SQL> select * from v$session;

It just stops there. Session won�t disconnect or any ORA-XXXX error.

Done the following checks,

-Initially thought of driver issue, so tried with multiple version of oracle
client,toad,oracle sql developer but issue persists.
- v$session shows that the above session in question is in status as
�INACTIVE� and state as �WAITING�
- tnsping from remote system and localsystem returns almost similar response
time (600 msec - 670 msec)
- Couldn�t find anything unusual in alert / trace also in listener log.

• Since I am able to run some queries, no need to bother about firewall side.
• Done some basic analysis using wireshark packet analyzer - couldn�t see any error. However could see that local system is getting �response data� from the database server.
• MTU set to different size , 1500,1400,Default etc with no luck.

I assume that it has something to do with network side. Your any inputs on troubleshooting this kind of issue will be very helpful for me.

Thanks.
G

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 12 2010 - 08:17:17 CDT