Re: To Resize a Datafile to a Smaller Size

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

--
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)