Re: ORA-04030: out of process memory

"Jesus" <jesus_angel_g_at_hotmail.com> wrote in message news:a0ff9777.0207220025.52bd98dc_at_posting.google.com...
> Hi,
>
> We have a Data Mart data base, including tables of 1,4,13 and 14
> millions of rows. When we try to execute one query which need data
> from all these four tables (all of then linked), we received the
> error:
>
> ORA-04030: out of process memory when trying to allocate 1040896 bytes
> (cursor work he,kllcqas:kllsltba) :-4030
>
> Althought we are not experienced DBA, we had tried increase the
> Db_block_buffers and Shared_pool_size, but we don't achieve any
> improvement.
>
> We are working with Oracle 8.1.6 over Unixware 7.1.1. Our principal
> Oracle parameters are:
>
> db_block_buffers = 64200
>
> shared_pool_size = 150000000
>
> parallel_min_servers = 2
> parallel_max_servers = 28
>
> large_pool_size = 66144000
> log_checkpoint_interval = 10000
> log_checkpoint_timeout = 0
>
> processes = 150
>
> log_buffer = 256000
>
> db_block_size = 8192
>
> sort_area_size = 100000000
> sort_area_retained_size = 100000000
>
> Thank you in advance for your help.
>
> Jesus.

ora-4030 means the dedicated session on the server runs out of memory. It strikes me you are using a way too high sort_area_size, sort_area_size is a *per process* limit, not a system-wide limit. I would decrease it with a factor 100 to 1M.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address