I have a large OLTP database in which daily 30 MB of data gets added. My rollback tablespace of 800 MB gets filled up every 15 days or so. I have to shrink each rollback segment manually to prevent the tablespace from extending and also to ensure large transactions are completed. Is there any way to avoid this so that the rollback segment can take care of itself?
If you are using Oracle 9i, then it is time to start using automated UNDO instead of the old fashioned rollback segments which can automate the handling of your rollback segments for you.
If you have to, or still want to use the old-style rollback segments, then you can have Oracle automatically shrink your rollback segments when they no longer need to be a large size by setting the OPTIMAL setting for your rollback segment. This can be done with a command similar to the following:
ALTER ROLLBACK SEGMENT rbsXX STORAGE (OPTIMAL yyK);
You can specify OPTIMAL in bytes, K(bytes) or M(bytes).
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.