This discussion is archived
7 Replies Latest reply: Jan 4, 2013 2:10 PM by BrendanP RSS

Understanding Memory Error

936481 Newbie
Currently Being Moderated
DB version : 11.2

Please help me to understand the Issue. Is database missing initialization parameter for max. allocate memory for Individual session ?

Sample anonymous block ran on database:

DECLARE
l_strings DBMS_SQL.varchar2a;
BEGIN
FOR indx IN 1 .. 2 ** 31 - 1
LOOP
l_strings (indx) := RPAD ('abc', 32767, 'def');
END LOOP;
END;
/

Expected Result : ORA-04030: out of process memory when trying to allocate 40932 bytes.

Actual result : Hanged whole multi-user database server and caused to reboot the server.

Thanks,
--Anan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
  • 1. Re: Understanding Memory Error
    sb92075 Guru
    Currently Being Moderated
    Oracle Dev. wrote:
    DB version : 11.2

    Please help me to understand the Issue. Is database missing initialization parameter for max. allocate memory for Individual session ?
    NO
    Problem Exists Between Keyboard And Chair!
    Sample anonymous block ran on database:

    DECLARE
    l_strings DBMS_SQL.varchar2a;
    BEGIN
    FOR indx IN 1 .. 2 ** 31 - 1
    LOOP
    l_strings (indx) := RPAD ('abc', 32767, 'def');
    END LOOP;
    END;
    /

    Expected Result : ORA-04030: out of process memory when trying to allocate 40932 bytes.

    Actual result : Hanged whole multi-user database server and caused to reboot the server.

    Thanks,
    --Anan
    Consider to NOT repeat this silliness ever again.
  • 2. Re: Understanding Memory Error
    damorgan Oracle ACE Director
    Currently Being Moderated
    This is a bug. Please open an SR at MyOracleSupport as it should to be addressed.

    I agree with SB that you shouldn't have done it. And that you should definitely know better than to alpha test code on a multi-user server, but that said Oracle should gracefully handle the exception without taking everything down.
  • 3. Re: Understanding Memory Error
    sb92075 Guru
    Currently Being Moderated
    damorgan wrote:
    This is a bug. Please open an SR at MyOracleSupport as it should to be addressed.
    or NOT

    http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams141.htm#REFRN10285

    "Total memory usage can grow beyond the value of MEMORY_TARGET. For example, memory is allocated to PL/SQL tables and varrays regardless of the value of MEMORY_TARGET as long as memory is available at the operating system level."
  • 4. Re: Understanding Memory Error
    damorgan Oracle ACE Director
    Currently Being Moderated
    I am well aware of the documentation ... but anything you can do in the Oracle database, no matter how insane, should not bring the database down and definitely should not force a reboot.

    From Oracle's standpoint generating an exception would be correct ... even if an ORA-00600 or ORA-07445. But freezing a system is a bug.
  • 5. Re: Understanding Memory Error
    936481 Newbie
    Currently Being Moderated
    Hi Damorgan and SB,
    Thanks for your comments and concerns about this Issue...
  • 6. Re: Understanding Memory Error
    sb92075 Guru
    Currently Being Moderated
    SQL> DECLARE
    l_strings DBMS_SQL.varchar2a;
    BEGIN
    FOR indx IN 1 .. 2 ** 31 - 1
    LOOP
    l_strings (indx) := RPAD ('abc', 32767, 'def');
    END LOOP;
    END;
    /  2    3    4    5    6    7    8    9  
    DECLARE
    *
    ERROR at line 1:
    ORA-04030: out of process memory when trying to allocate 40932 bytes (koh-kghu
    call ,pl/sql vc2)
    
    
    SQL> 
    only threw error.
    did not crash Oracle or OS!
  • 7. Re: Understanding Memory Error
    BrendanP Journeyer
    Currently Being Moderated
    11.2.what? I'm guessing 11.2.0.1 since my XE 11.2.0.2 handles it ok; .0.1 seems to be generally buggy.

    Edited by: BrendanP on 04-Jan-2013 14:01
    This is what I got, which indicated that the transaction failed but that the database did not go down:
    SPOOL Mem
    DECLARE
    l_strings DBMS_SQL.varchar2a;
    BEGIN
    FOR indx IN 1 .. 2 ** 31 - 1
    LOOP
    l_strings (indx) := RPAD ('abc', 32767, 'def');
    END LOOP;
    END;
    /
    SELECT * FROM DUAL;
    SPOOL OFF
    DECLARE
    *
    ERROR at line 1:
    ORA-04030: out of process memory when trying to allocate 40932 bytes (koh-kghu
    call ,pl/sql vc2)
    
    
    
    D
    -
    X
    However, I was running another job at the same time, and this is what happened to that job:
    ERROR:
    ORA-03114: not connected to ORACLE
    
    
    BEGIN
    *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel
    
    
    Elapsed: 00:10:53.05
    SQL> 
    So, perhaps there is some confusion here, and the rogue transaction actually just uses all the available common memory, thus crashing other transactions, but not the database, and therefore it may not be a bug?

    Edited by: BrendanP on 04-Jan-2013 14:08
    Although I suppose it shouldn't have disconnected me, just dropped the transaction?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points