Re: error ORA-03297

Hi,
You try to resize a datafile smaller using the command:

    SQL> ALTER DATABASE DATAFILE 'datafile RESIZE nnn;

Then, you receive the error ORA-03297:
This occurs because the datafile is fragmented, with objects in the datafile in the portion of the datafile that is to be resized.

To solve the problem, do the following:

1. Do a complete export of all objects in the tablespace
2. Take tablespace offline
3. Drop the tablespace
4. Recreate the tablespace to the desired size
5. Use the export from step 1 to re-import the data.

The datafile cannot be resized smaller with the ALTER DATABASE DATAFILE RESIZE command if objects are in the space to be removed. You can achieve the same effect by recreating the tablespace to the smaller size.References

satar_at_my-dejanews.com wrote:

> That error message means that data resides on the datafile where you're trying
> to make it smaller.
> To solve this problem, try a different size..
> ALTER DATABASE DATAFILE '/u01/oradata/y2k/users01.dbf' RESIZE 50M;
> Does that work? no..then continue
> ALTER DATABASE DATAFILE '/u01/oradata/y2k/users01.dbf' RESIZE 60M;
> Does that work? no..then continue
> ALTER DATABASE DATAFILE '/u01/oradata/y2k/users01.dbf' RESIZE 70M;
> Ect..
>
> Hope it helps,
> Satar
>
> In article <362B1F97.30B8AA03_at_ariadne.it>,
> Giuseppe De Donno <dedonno_at_ariadne.it> wrote:
> > Hi,
> > If I try to modify the size of a datafile, I'll have the following
> > error:
> > ORA-03297: file contains 184545 blocks of data beyond requested RESIZE
> > value
> >
> > I need to reduce the size of this datafile, I done an export and the an
> > import, but the problem remain because during the creation of index the
> > size of datafile increase and then the real space requested is less than
> >
> > the space allocated!
> > What can I do?
> >
> > Thanks
> > best regards.......Giuseppe
> >
> >
>
> --
> Oracle DBA/UNIX System Admin
> Advanced Enterprise Solutions
> (949) 756-0588
> Oracle Re-Seller
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Wed Oct 21 1998 - 00:00:00 CDT