Re: Strange undo behavior

On Oct 17, 10:10 am, Chuck <skilover_nos..._at_bluebottle.com> wrote:
> -----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?

Because you left the datafile(s) in the undo tablespace of interest set to autoextend?

> 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 - 11:44:50 CDT