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

Hi, John,
The message "File contains ... blocks beyond requested resize value" means at the moment this command is issued, the sum of sizes of all rollback segments exceeds the requested resize value. This can happen even in database which does not have any active transactions: simply because RBS have some size. Yes, if you specified OPTIMAL during rollback segment creation, Oracle will try to resize them automatically. However, the default is no resize and even after resize the rollback segment has some nonzero size (at least two extents). So, what you need to do:
1. Check the STORAGE for your rollback segments, i.e. INITIAL, NEXT, MINEXTENTS, OPTIMAL. 2. Resize them manually.

3. Take unnecessary rollback segments offline (by ALTER ROLLBACK SEGMENT segment_name OFFLINE).

4. Drop the segments taken offline at step 3.

5. Check the sum of sizes of the remaining rbs by quering DBA_SEGMENTS 6. Resize the datafile.

Seva

P.S. E-mail me if you have any questions.

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. I figure that this means that there are still
> active transactions in the rollback file. The question is, how do I
> commit these transactions? I really don't care about any data that might
> be in the file. I've tried shutting down the database and bringing it
> back online again but this doesn't help. I got myself into this fix by
> setting autoextend ON...
>
> 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.
  Received on Mon Oct 13 1997 - 00:00:00 CDT