Can't return data from SQL/Plus and Toad but can from SQL Developer

Question

This one has me stumped. We are running an Oracle 11g on Amazon EC2 and I can connect and query fine with SQL Developer but with Toad and SQL/Plus I can connect but when I try to do a simple select out of v$session for example it gives me an ORA-03135 after a while. Here is what I have looked at/tried so far (in no particular order):

• Looking in the listener.log file and seeing that all three connect fine
• Turning the windows firewall completely off on the server (nothing changed)
• TNSPing from my machine resolves
• Port scanned to make sure port 1521 was open
• Used telnet to make sure 1521 was open
• Compared listener setup to oracle db on another project

• Philᵀᴹ: Weird. Is there anything in the alert.log on the DB server? Are you using the same connect string in all 3 clients (IE: connecting by name that's in your tnsnames.ora, rather than host:port)
• Xaroc: Don't see anything useful in the alert.log file for the database. I am using a TNS based connection for all 3 of them. I have tried using direct in Toad but it doesn't make a difference.
• Mat: Any chance this could be a client driver issue? What versions do you have installed on your client machine?
• Xaroc: I have both the full and thin client versions of 11g installed. FWIW I can connect to other 11g instances on Amazon with Toad with this configuration.
• user23590: have you check the database status? not kiddin I tried everything but that... select instance_name, status from v$instance

Answer 1

Sounds like the network buffer is being filled but not flushed. SQL*Net performance in this area is often managed by setting tcp.nodelay=yes in sqlnet.ora; also look at the send_buf_size and recv_buf_size parameters.

SQL*Dev working and not SQL*Plus are what pointed me to this type of troubleshooting approach. SQL*Developer uses jdbc behind the scenes to do its fetching.

hth!

• Xaroc: I set tcp.nodelay=yes and then set both send_buf_size and recv_buf_size according to the guidelines here: dba-oracle.com/t_bdp_sqlnet.htm and it worked. Thanks for the help.