Recently I had an Oracle SQLPLUS client hanging session issue. On analysis, firewall between the Oracle database server and Unix application server was closing the Oracle session leading the hanging session in the Client side. When server completes the request, there is no physical connection exists to send back the return code. Client which does not aware the session being cut, tend to wait infinitely for the return code from the server, that leads to an Hanging session issue for the client.
To fix this, we could create a sqlnet.expire_time = 10 parameter in SQLNET.ora file of Oracle database server which keeps pinging the client for every 10 minutes (this should be set more than the firewall counter for closing the idle connection), that makes firewall reset its counter that closes an idle dead connection.
