Connecting to Oracle when server out of resources/sessions

Today I have experienced an Oracle TNS-12540 error (TNS: internal limit restriction exceeded) with our 8i server (8.1.7.3) running on Windows 2000 (SP3 if I am not mistaken).

My research led me to believe that this error is probably due to either the server itself being completely out of physical memory
(allowing no new server processes to be created - checking the task
manager partly confirmed this hypothesis, as the server itself *was* out of physical memory and using the swap file heavily) or because the max_sessions parameter was reached.

The problem is that I wasn't able to pin-point exactly what caused this as any new connection attempt to the server (even connecting as internal from the server command prompt itself) weren't possible
(svrmgrl prompted that error at every connection attempt).

At the end I had to resort to shutting down the Oracle Instance and restart it to solve this issue (it was either that, or asking some of the users to disconnect).

My question is if whatever or not it is possible to connect to Oracle when the max_sessions parameter has been reached or when Oracle is out of memory resources. I hope that the design of the Oracle Instance reserves one connection to be used by the DBA for emergency times like this.

If not, what do you recommend doing at such times short of restarting the service? What would you do?

(Btw, we are *not* running the server with MTS configuration).

Thank you.

David Yahalom, ORACLE DBA,
Data Integration Specialist. Received on Sun Apr 27 2003 - 14:16:21 CDT