Re: Cannot deallocate the rollback segment

>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

That would be fine to do the above steps, but what do you do with the system rollback segment? You cannot take it offline or delete it and the system rbs is in the same tablespace as the other rollback segments. Here lies the problem.

spencer wrote:

> "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 Fri May 05 2000 - 00:00:00 CDT