Q
Problem solve Get help with specific problems with your technologies, process and projects.

Automating rollback segment handling

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close