Re: To Resize a Datafile to a Smaller Size

Dino,

You could..

1. Identify the index segments in the tablespace that go beyond your desired high water mark
2. Move those indexes to other tablespace... alter index xxx rebuild tablespace yyyy;
3. Coalesce the tablespace
4. Move the indexes back into the tablespace

I've not tried this, but I expect it might reset the high water mark.

Let me know how you get on.

Richard

Dino Hsu <dino1_nospam_at_ms1.hinet.net> wrote in message news:hsrcmtgn7j5d2op3phjviuborf9bcclg9o_at_4ax.com...
> 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:54:46 CDT