We are running a simple queries against partitioned tables and finding that TOAD hangs and then kills the session. When the same query is run in Oracle SQLPLUS it returns fine.
Toad does not have any command that kills sessions unless manually specified to kill or drop by the user.
This is an issue related to the environment's network/firewall settings.
Disable or adjust firewall on the network to stop from cutting off database connections.
Refer to SOL51309 Session is dropped and Toad hangs when trying to click on data tab in schema browser.
https://support.quest.com/SUPPORT/index?page=solution&id=SOL51309
For specific situation with running query on partition tables:
We followed this metalink note found. The proposed solution of disabling 'SQL Inspection' worked for us:
Note: If you cannot find a setting for SQL Inspection, please try looking for and disable "Support for Oracle SQL Net" (sonicwall firewall setting)
Subject: Remote DESCRIBE Over Sqlnet For Table With Large Number of Columns Hangs
Doc ID: 859480.1 Type: PROBLEM
Modified Date : 16-JUL-2009 Status: MODERATED
In this Document
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Net Services - Version: 10.2.0.1 to 10.2.0.4.0
This problem can occur on any platform.
Symptoms
Sqlnet client connections over WAN hang when doing operations that may take some time to process in the database. In this case it was DESCRIBE of a table with a large number of columns
Cause
Though this wasn't confirmed from our side
the customer has stated that 'sql inspection' service was turned on for the firewall, and it was severing the connection without notifying the client, which caused it to hang waiting for response.
Solution
- Do server and client sqlnet tracing to prove the connection is severed.
Note 219968.1 SQL*Net, Net8, Oracle Net Services - Tracing and Logging
-Disabling 'sql inspection' service on the firewall solved the issue. please ask your network admin to do that after confirming from the net traces
