Re: UNDOTBS01.DBF file size

From: Chuck <skilover_nospam_at_softhome.net>
Date: Fri, 26 Aug 2005 10:04:36 -0400
Message-ID: <1125060965.bc8283e3a52ac5e82351a2ee2f54a199@bubbanews>

-----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----- Received on Fri Aug 26 2005 - 09:04:36 CDT