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

Posted on 2006-03-23
Last Modified: 2013-12-11
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?
Question by:joekolman
    1 Comment
    LVL 16

    Accepted Solution

    by: (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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Create two table and a foreign key? 4 51
    jobsrunning 3 56
    Oracle SQL Select Statement 19 46
    Need help with Oracle syntax 4 32
    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now