Re: ALTER DATABASE DATAFILE...ORA-03297...HELP!

Hassan Abba wrote:
>
> Try:
>
> ALTER DATABASE DATAFILE 'D....' OFFLINE;
>
> This can only be done if the media recovery is enabled.
>
> Then try to resize it.
>
> Regards
>
> Hassan Abba
> Database Developer/Administrator
> Soft Options UK Ltd
>
> John Dunn wrote:
>
> > Greetings:
> >
> > I'm hoping someone can help me or point me in the right direction..
> >
> > Here's what I'm trying to do:
> >
> > I'm trying to resize a datafile in my RBS (Rollback) Tablespace from
> > 400M to 150M. When I issue the command
> >
> > ALTER DATABASE DATAFILE 'f:\mydatafile.dbf' RESIZE 150M
> >
> > I get the "ORA-03297: File contains 4100 blocks of data beyond requested
> > resize value" message. How can I resize the datafile? Or how do I drop it
> > and re-create it?
> > Thanks for any pointers and/or references to the documentation. I've
> > looked but it's rather muddy.
> >
> > My environment: Workgroup Server 7.3.2 on NT 3.51.

You will not be able to offline tablespace which has active rollback segment. And ALL of your user rollback segs are in one tablespace, right.
First create rollback segment in another tablespace, make sure that your session uses this rollback then bring the tablespace offline. In this case I would drop the rollback tablespace and create it again with new storage parameters.
Halina Received on Thu Oct 09 1997 - 00:00:00 CDT