Re: To Resize a Datafile to a Smaller Size

Dear all,

A related question is:
How to find the schema objects hosted by a tablespace?

Dino

On Tue, 31 Jul 2001 09:54:46 +0100, "rshome" <rshome_at_ntlworld.com> wrote:

>Dino,
>
>You could..
>
>a) Identify the index segments in the tablespace that go beyond your desired
>high water mark
>b) Move those indexes to other tablespace... alter index xxx rebuild
>tablespace yyyy;
>c) Coalesce the tablespace
>d) 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 Wed Aug 01 2001 - 01:40:09 CDT