ora-04030 out of process memory

[khoshtip_uk]

Hi,
we are having problem with this big report it seems that the report is using huge amout of memory we have doubles the amount of pga_aggregate_target to 239mb still getting this error. is there anyway to calculate the value for pga_aggregate_target for this particular session or sql statment?
or figure out how much of it its using ?

[reydp]

have you set your WORKAREA_SIZE_POLICY to AUTO?

[khoshtip_uk]

yep

[reydp]

select sid,name,value
from v$statname n,v$sesstat s
where n.STATISTIC# = s.STATISTIC# and name like 'session%memory%'order by 3 asc;

[khoshtip_uk]

we ran the report one more time and monitored the sga usage through TOAD the report is requiring more than 1GB of SGA_AGGRe... , we decided to break the report down.

[PAVB]

Let me just add my two-cents...

Fact: Your process is exhausting O/S Process Memory.

Is it Unix/Linux?... it could be ulimit related

Are you resorting to Bulk Collect, it worked on 9i and it's failing on 10g? If this is the case you have bumped into the same bug we bumped a couple of month ago. Solution: Remove Bulk Collect.

[khoshtip_uk]

there is a limit on amount of memory a single process can consume on the box, we dont want to increase that limit to 1.7gb yet, we think there is a problem with the way sql statment has been writen,