How to decrease size of Rollback Segments Tablespace

Hi there,

  i have the following problem: On my Oracle Server (Win2000, 9i, Archived Log Mode, Rollback Segments) my rollback segment tablespace has grown 10GB big and I want to make it smaller. The tablespace contains just 1 data file that is 10GB big.

  Normally with a standard data file I use OEM to "reorganize" the tablespace, like a defragmentation, and then decrease the size of it. However with the rollback tablespace OEM tells me reorganization is not (yet) supported. And I am unable to just decrease the size of the file because I get the all famous "ORA-03297 file contains used data beyond requested RESIZE value" message. However, only 4% of the file are actually used so there is the potential to decrease it quite a bit, its just that the data blocks occupied in the file lie more towards the end of the file.

  Any idea what I can do ? I already decreased the sizes of the rollback segments, but that didnt release enough blocks in the data file. So I seem stuck. Reducing the filesize would also reduce the size of my physical full backups, so it would be a double gain... I am hoping for applicable suggestions on here,

  thanks,

André
:) Received on Fri May 28 2004 - 08:51:50 CDT