7 Replies Latest reply: Jan 4, 2013 4:10 PM by BrendanP RSS

    Understanding Memory Error

    Herndon
      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
          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
            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
              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
                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
                  Herndon
                  Hi Damorgan and SB,
                  Thanks for your comments and concerns about this Issue...
                  • 6. Re: Understanding Memory Error
                    sb92075
                    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
                      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?