Strange undo behavior

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Tue, 17 Oct 2006 14:10:17 GMT
Message-ID: <dv5Zg.1883$3C6.631@trnddc04>

-----BEGIN PGP SIGNED MESSAGE-----

Hash: SHA1

Oracle 9.2.0.5
Solaris 8

An undo tablespace on one of my databases has been acting strange for the last week. The undo retention period is 2 hours. Occasionally the space used in the undo tablespace will go from a few megabytes to almost 10g even though a query of v$undostat for the same time period shows only 38m of undo has been created. Can anyone explain what is going on?

How do you explain the used space in the undo tablespace going from < 10 meg to almost 10 gig in a space of two hours where v$undostat shows that < 50m of undo blocks have been written?

SELECT SUM (undoblks) * 8192 / 1048576 mb_used FROM v$undostat
WHERE begin_time >= SYSDATE - 2 / 24

MB_USED
- ----------
38.4296875

1 row selected.

In that same time period, undo usage went from 10m to 9.5g.
-----BEGIN PGP SIGNATURE-----

Version: GnuPG v1.4.5 (MingW32)

iEYEARECAAYFAkU05EkACgkQzIf+rZpn0oRCFgCcCBgUbdyvpf8jPWcWR139zfev zAkAn0QzLSzlIJrMJXoWFqYXUvCsgZ9g
=UfZF
-----END PGP SIGNATURE-----
Received on Tue Oct 17 2006 - 09:10:17 CDT