Hi Karl Thanks for the script. According to the script i need a undo tablespace with a size of 34Mb. However past few days through my monitoring script i am getting a errro message saying my undo tablespace is above 90% full. And i ran another script to find undo hoggers SELECT TO_CHAR(begin_time, 'DD-MON-RR HH24:MI'), TO_CHAR(end_time, 'DD-MON-RR HH24:MI'), tuned_undoretention, maxquerylen, maxqueryid,UNDOBLKS FROM v$undostat ORDER BY end_time and this gives me a sqlid (maxqueryid) where the undoblks value is 13841. Should i start tuning the query in question ? regards Hrishy --- On Wed, 8/7/09, Karl Arao <karlarao@xxxxxxxxx> wrote: From: Karl Arao <karlarao@xxxxxxxxx> Subject: Re: Sizing Undo tablespace To: hrishys@xxxxxxxxxxx Cc: oracle-l@xxxxxxxxxxxxx Date: Wednesday, 8 July, 2009, 8:12 AM Hi Hrishy, You could check this Metalink Note... How To Size UNDO Tablespace For Automatic Undo Management Doc ID: 262066.1 You'll get from the note the query below: SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), (SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat), (select block_size as DBS from dba_tablespaces where tablespace_name= (select upper(value) from v$parameter where name = 'undo_tablespace')); - Karl Arao http://karlarao.wordpress.com On Wed, Jul 8, 2009 at 2:50 PM, hrishy <hrishys@xxxxxxxxxxx> wrote: Hi I am trying to size my undo tablepsace by looking at the undo rates.i.e the column UNDOBLKS in v$undostat. Value for undoblks for particular sql is 13841 and my database size is 8kb.Does this mean that i need to have a undo tablespace size of select 8192*13841/1024/1024 from dual 108Gb ? Not the particular sql does a corelated update of mere 72762 rows and the figure of 108Gb doesnt make much sense .This is a JDBC app and i dont have much idea if this sql is in some for loop or something. regards Hrishy regards Hrishy