pga crashed my developement database

[steeve123]

database: 10.2.0.2.0
workarea_size_policy = auto
pga_aggregate_target = 194M

1 session was consuming 1.1 gig of pga memory today.
sys@ORCL> SELECT NAME, sum(value)/1024/1024 Mb
2 from
3 v$sesstat s, v$statname n
4 where
5 n.STATISTIC# = s.STATISTIC#
6 AND NAME LIKE '%memory%'
7 GROUP BY NAME;

NAME MB
-------------------------------------- ----------
session uga memory 20.4857979
session uga memory max 40.3968544
session pga memory 1182.88043
sorts (memory) .005688667
workarea memory allocated .000658035
session pga memory max 1230.14667

My little windows 2003 system with 2 gig of ram did not take it good.

I identified the session and i think there was a infinite loop or something inserting data in an array.
Anyway, we will fix the code soon today.
We are in a developement envirronement of course.


Question:
Is there a way to configure OEM to send an email when the pga goes besserk? So i could kill the session before it crashed the database?
Some kind of treashold or something.

I killed the session and the database was not returning to normal, so i shutdown abort it. Would a orakill be better?

Is there a way to set maximum pga per session?

[davey23uk]

there is a maximum pga per session, its 5% of the total