Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: too many session open and you can't log in?

RE: too many session open and you can't log in?

From: Mary Bahrami <>
Date: Fri, 16 Mar 2007 11:43:57 -0700
Message-ID: <>

I've had similar problems, in most cases caused by locking (caused by devs w/direct access). But you need to be able to get in to see what's going on before you resort to bouncing the db; Gui tools are generally useless as they can't connect either....there was a script posted a while ago on this list that would always keep a session open, but I've lost it. (any one have it?)  

If you can't login, you can kill a few sessions from the OS until you can get in. I run a few 'check everything' scripts for later perusal in case users are screaming and want you to bounce it NOW! Output lists running sql, v$session info (check for more than expected 'ACTIVE' sessions), cursors, events, waits, etc. Also look at ltom from metallink....there are some useful scripts in there. If you run it occasionally when things are good, you'll recognize the problem faster.  

[] On Behalf Of Charles Schultz Sent: Friday, March 16, 2007 7:14 AM
Cc:; Subject: Re: too many session open and you can't log in?  

Good point about the scheduler. I locked up our 10201 production database by accidentally flipping the plan to internal_quiesce - it is the stinking default if you change the time for the plan. Grr!

On 3/15/07, rjamya <> wrote:

What about using os debugger to connect to an existing process, dump hanganalyze (or systemstate and then run ass.awk on it) to see the root cause and start killing from that point onwards rather than shooting the whole system down? In wither case a systemstate would be useful for a post-martem.  

I also noticed that in 10203 (and perhaps in 10202), the scheduler uses internal_quiesce resource plan, activated it at 10pm and then you see lot of sessions (which appear hung) waiting on 'resmgr:become active' wait event. In this case, the solution is a simple alter system to set the resource manager plan to a null value.  

Since Ryan didn't mention version, I don't know if it is his problem.  

Another situation I have seen when things do get hung is when a logon trigger goes invalid and before it is validated by a dba, incoming sessions do try to compile it and since (usually) they don't have privs to compile logon triggers, everything seems to hang. Personally I have experienced this in 9204.  


On 3/15/07, Dennis Williams < > wrote:


You might try logging in with sqlplus '/ as sysdba'.

I agree with the advice to use kill -9.

If you do get in with sqlplus you're probably going to do a shutdown abort.  

Charles Schultz 

Received on Fri Mar 16 2007 - 13:43:57 CDT

Original text of this message