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

  • From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
  • To: rjamya@xxxxxxxxx
  • Date: Fri, 16 Mar 2007 09:14:01 -0500

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 <rjamya@xxxxxxxxx> 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

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 <oracledba.williams@xxxxxxxxx > wrote:
> Ryan,
> 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

Other related posts: