Re: UNDOTBS01.DBF file size

Chuck wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Matthias Hoys wrote:
> > "Jim" <mail_at_lost.com> wrote in message
> > news:23qtg1h1omivphpq5onj8p1lblqh5ecicv_at_4ax.com...
> >
> >>Win2k, Personal Oracle Database 10g Release 10.1.0.2.0
> >>
> >>First of all I am not a Oracle techie so please excuse the lack of
> >>technical knowledge. I have the following problem.
> >>
> >>A disk was running low on space to I did a check of file sizes. The
> >>UNDOTBS01.DBF turned out to be 7gb. That is huge given that the files
> >>in which the data is stored is only 1.5gb. My questions are as
> >>follows:
> >>
> >>- why is the UNDOTBS01.DBF file so big?
> >>- how can I reduce the size of this file?
> >>- what can I do to ensure that it doesn't get this big in the future?
> >>
> >>Thanks.
> >
> >
> > See the thread some messages below regarding Undo Management.
> >
> > Matthias
> >
> >
>
> The datafile probably has autoextend turned on and some large
> transaction caused it to grow. Tell your DBA about it. He/she may not be
> aware, or may have intentionally set it that large to handle some long
> running transaction. (Yes it might actually need to be larger than the
> rest of the database depending on what the DBA was trying to accomplish).
>
> If it's not a production database, and there is no DBA, you could shrink
> it with this command, but it requires DBA privilege.
>
> alter database datafile 'fully_qualified_file_name_goes_here' resize
> 2048064k autoextend off;
>
> - --
> To reply by email remove "_nospam"
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.0 (MingW32)
>
> iEYEARECAAYFAkMPIXMACgkQzIf+rZpn0oSZ2gCfdHpo/yf4ILZ+X1qKadmB8GR/
> kV4AmwZ7WdhQ9OaY9vdWLz2jUKXLPBWq
> =S6Wc
> -----END PGP SIGNATURE-----
An area no one has yet considered is the value for the undo_retention parameter; this directly affects the size of the UNDO tablespace. Possibly this is set larger than necessary; a quick chat with the DBA should put this issue to rest as he/she can explain the rationale behind the undo tablespace size.

David Fitzjarrell Received on Fri Aug 26 2005 - 10:34:15 CDT