DB crashed with ORA-4030 on DBW0 process

From: Steve Perry <sperry_at_sprynet.com>
Date: Wed, 19 Mar 2003 16:40:00 -0600
Message-ID: <b5ark1$m7b$1@slb1.atl.mindspring.net>

Our database crashed over the weekend with the following lines from the trace.

ORA-00449: background process 'DBW0' unexpectedly terminated with error 4030
ORA-04030: out of process memory when trying to allocate  bytes (,)
ORA-04030: out of process memory when trying to allocate 8512 bytes (pga

heap,ksm stack)
The statement was insert into "/BIC/EZPCA_C03" ... select ... from ... the table "/BIC/EZPCA_C03" has 73 partitions and 13 bitmap indexes.

I found this on metalink (bug 1428288) and it looks like it abended due to a combination of large sort_area_size, DML against a partitioned table with abnormally high number of bitmap indexes on it. Oracle will defer index maintenance, for bitmap indexes, until the dml operation is complete. It buffers the index entries in the session's PGA memory and the formula for memory usage is sort_area_size x #of indexes x #of partitions.

According to that, then I need 19,901,972,480 bytes for the job to run. ( 20 meg (sort_area_size * 73 partitions * 13 bitmap indexes) . I'm sure I missed something.
This formula won't be used for "insert into.. values ( )", but will for "insert into ... select * from ...". Is this correct? Any other conditions that would cause this formula to be used.

The system is an SAP BW on 8.1.7.4.1/Win2K

Can somebody add to this?

Thanks,
Steve Received on Wed Mar 19 2003 - 16:40:00 CST