Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Memory processes

Re: Oracle Memory processes

From: Klaus Zeuch <KZeuchnospam_at_hotmail.com>
Date: Wed, 24 Jan 2001 15:24:52 +0100
Message-ID: <94mrpr$pa3$1@papyrus.erlm.siemens.de>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US