Re: Connecting to Oracle when server out of resources/sessions

I have checked the alert log. Nothing there. I was able to pin-point the problem by writing a small program that dumps the content v$session and V$sesstat tables to a text file on the server's hard drive at specified intervals.

Today the database experienced the same behavior and since the program I wrote was monitoring in the background, I was able to find the problematic session which have caused this mess..

Apparently, an SQL query generated by a user caused a memory leak in Oracle causing our server to virtually "bleed to death". In a matter of minutes after the query ran the Oracle.exe process finished every Mbyte of the available 1GB of RAM. I am still working on determining what exactly in the SQL that was running caused this to happen.

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EAC400F.F976D070_at_exxesolutions.com>...
> DataOrb wrote:
>
> > 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.
>
> Do you read the alert log? It should all be detailed there.
Received on Mon Apr 28 2003 - 14:36:07 CDT