Re: To Resize a Datafile to a Smaller Size

Dear all,

Yes, I dropped all objects, reduced sizes of the datafiles, and then re-created all objects back. This works. I don't know why coalesce is necessary?

Dino

On Tue, 31 Jul 2001 08:56:52 -0400, Chuck Hamilton <chuck_hamilton_at_yahoo.com> wrote:

>-----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-----
Received on Thu Aug 02 2001 - 08:51:46 CDT