undo tablespace keeps on growing
apps_bnSep 2 2009 — edited Sep 3 2009We have a 3rd party application running on our oracle(10.2.0.4) database running on 64bit solaris(sparc) machine.
Since few days my undo started going up. I increased the size from 2g to 5g. Still it is at more then 90%levels.
I ran this query to see which session is using maximum undo
SELECT a.sid, b.name, a.value
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic#
AND a.statistic# = 176
ORDER BY a.value DESC
From this i found the session which is using max undo.
When i query this sid from v$session i see this is inactive
my undo retention is set to 9000 and undo management is auto
how can i check if i have expired undo blocks that are not being used