Re: Cannot deallocate the rollback segment

"Allen" <allen.brost_at_motorola.com> wrote in message news:3911DC23.F483E55B_at_motorola.com...
> I am trying to deallocate the rollback segment and I am
 getting the following
> error:
>
> "ORA-03297: file contains used data beyond requested RESIZE
 value"
>
> This is not true. The data files size is 2G and the amount of
 used space is
> 40meg.
> I have resized the rollback segment before (at least I thought
 I did) so I am
> not sure what the problem is this time, unless something is
 hosed. Any way to
> force the issue? Any help?
>
> - Allen
>
>

Allen, this is a documented behavior, working to the spec.

The 40MB of space allocated to objects within the tablespace can be located anywhere within the datafile. As I understand it, when you attempt to resize a file to a smaller size, Oracle attempts free space starting from the "end" of the file, back towards the "front" of the file. If it encounters any data blocks (i.e. allocated space) within the portion of the file space to be "freed", Oracle will generate the error message you received.

The key here is to figure out which objects are using the space in the data file, and either remove or relocate those objects to somewhere other than the area of the data file you are attempting to free.

If the only objects in the data file you need to resize are rollback segments, then you could try "shrink"ing the rollback segments, and then resizing the data file. That would be the easy way.

If that doesn't work, then you'll need to follow a more involved procedure. Something like this should work:

1. perform a complete backup of the database
2. bring online an alternate rollback segment (in different tablespace)
3. take the offending rollback segment(s) offline
4. drop the offending rollback segments
5. coalesce free space in the offending tablespace
6. resize the data file
7. re-create the rollback segments
8. bring the newly re-created rollback segments online
9. perform another complete backup of the database

HTH. Spencer Received on Thu May 04 2000 - 00:00:00 CDT