To Resize a Datafile to a Smaller Size

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 Received on Tue Jul 31 2001 - 03:42:59 CDT