Printable View
I have a rollback tablespace datafile. When I look at the tablespace through GUI tool it shows 2750m allocated 1813.73m used. When I am trying to reduce the size of the datafile to 2200m I get an error:ORA-03297 'FILE CONTAINS 115845 BLOCKS BEYOND REQUESTED RESIZE VALUE'.
I've already tried to coalesce that tablespace. It didn't change anything.
What is going on? What do I do?
Seems you have segments spread in the portion you are trying trim off. Thats means Just because its showing less means you could resize it. It depends on where the segments have been spread.
You can give a try wehrn there is nothing in Rollback, I mean whenever you bounce database.
03297, 00000, "file contains used data beyond requested RESIZE value"
// *Cause: Some portion of the file in the region to be trimmed is
// currently in use by a database object
// *Action: Drop or move segments containing extents in this region prior to
// resizing the file, or choose a resize value such that only free
// space is in the trimmed.
Sheddy, you mean I can try to do the same resize thing when through server manager? If that is correct, what state my database shall be open in?
No big deal. you can find the rollback segments that doesn't have active transactiopns and drop couple of 'em and then try resizing it. it should work. You don't need to bring database down.
What you're describing is natural behavior for rollback tablespaces. If you look at tablespace manager it may show you the segments that are at the end of the datafile.
Even if you do drop the offending segments, your tablespace will be back to the behavior in short order. The proactive method is to size rollback segments appropriately to minimize extensions. Check the documentation or tuning books to get the scoop. Do this primarily by setting minextents to 20, and picking an extent size such that 20 of them will accomodate your largest average transaction. Set optimal = minextents*initial.