Oracle 10g lockup: heap exceeds threshold only indication in alert log

Oracle 10g on AIX with 6 started databases locked up.  Users in session could continue processing but no new users could sign in.  SQLPLUS froze after password was entered even when trying to sign in as sysdba.

Alert log showed at time of lock up  a message "heap (2949) exceeds notification threshold (2048)".  Appeared to be trying to load an object into SGA with name like XDB.xx.

It is possible that an import was running updating tables that one or more users could have been accessing, but alert log indicates successful import.

In trying to resolve problem,  tried to stop listener to prevent attempted sign ins,
but "lsnrctl stop" also hung. Seemed like databases were down, but in trying to
strart databases, got messages to stop them first, so, depending on where you looked and what you did you got conflicting information as to state of databases.

Finally did brute force dbshut and dbstart.  

Why I am getting heap size exceeded messages, and what is causing them?
Could whatever caused the messages cause an apparent lock up?
joekolmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RCorfmanCommented:
Metalink.oracle.com (Oracle's support site) has an article regarding this.  Note: 330239.1

I suggest reading the entire note. If you are using an spfile, they suggest trying (as sysdba):
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;
SQL> shutdown immediate SQL> startup
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.