Re: To Resize a Datafile to a Smaller Size

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, 31 Jul 2001 16:42:59 +0800, Dino Hsu <dino1_nospam_at_ms1.hinet.net> wrote:

>Dear all,
>
>An index tablespace has three datafiles, one of which is as big as
>8G in size, but the indexes are only 250M. I guess the datafile
>became so big because it was B-Tree indexes that were created, and
>later changed into Bitmap indexes, and the cardinality of the data
>are very low. I want to decrease the datafiles so that the off-line
>backup can be
>easier. When I do it with DBA Studio (SHOWSQL: ALTER DATABASE
>DATAFILE 'D:\TWDM\DATFILE\TSORDERIDX1.ORA' RESIZE 1024M), it
>prompts error
>ORA-03297 after a while.
>
>I checked <<Oracle Error Message>>:
>
>ORA-03297 file contains used data beyond requested RESIZE value
>
>Cause: Some portion of the file in the region to be trimmed is
>currently in use by a database object.
>
>Action: Drop or move segments containing extents in this region
>prior to resizing the file, or choose a resize value such that only
>free
>space is in the trimmed.
>
>The Oracle technical support tells me there is not way to reset the
>high water mark in a datafile, the only way to solve this is to drop
>all objects in the tablespace, drop the tablespace, and then
>re-create the tablespace. Anyone has better ideas than this?
>
>Dino

Try dropping and recreating all of the indexes in the tablespace. Then shrink the datafile(s).

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBO2arCGm5A4kkb4ZsEQJLvQCfQ2zZMzMtvLtXQf637m7AmvGlaicAn1a2 nfFz2+MlQ6nJAOzBTSl05Xzx
=aaYI
-----END PGP SIGNATURE-----

--
Chuck Hamilton
chuck_hamilton_at_yahoo.com

"Do not be deceived, God is not mocked; 
for whatever a man sows, this he will also
reap." (Gal 6:7 NASB)