Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Memory processes
For "automated killing" you could create a stored procedure and restart it via dbms_job in short intervals.
The procedure can select the memory consumers with a query like
SELECT sess.sid, serial#, username, value || 'bytes' "Current UGA memory"
FROM v$session sess, v$sesstat stat, v$statname name WHERE sess.sid = stat.sid
AND stat.statistic# = name.statistic#
AND name.name = 'session uga memory'
AND username is not null
AND bytes > your_limit
and then kill the session with execute immediate / dbms_sql
Klaus
Kumar AN <af281_at_city.ac.uk> schrieb in im Newsbeitrag:
94m15l$5ue$2_at_canard.ulcc.ac.uk...
> What is the best way of going abouts the problem of having processes on an
> Oracle database killed if they exceeed a certain amount of memory usage?
>
> I know I can use 'top' to ascertain if Oracle processes are going over a
> certain number of Mb, but does anyone have a script or something similar
> which will regularly analyse a system for processes using over a certain
> amount of memory and then kill the process?
>
> 2 points I would like to clarify here.
>
> 1) The database user is 'oracle' and I run the database and no one else
does.
>
> 2) It was a recommendation from Oracle themselves that in this particular
> case, Oracle processes taking up over 520Mb each should be killed. I have
> been doing this manually with no detrimental effect on the database. All I
> want to do it automate this now
>
> PS: This is a temporary fix till we, in conjunction with Oracle, figure
out
> a better long-term solution
>
> Thanks
>
> Arv
Received on Wed Jan 24 2001 - 08:24:52 CST