Unanswered: IMPORTANT --- Need your valuable suggesstion on rollback tablespace
Need help from Oracle Experts,
This is a query related to Oracle 8i database. We are troubleshooting issues with rollback segment errors. In our environment we are NOT getting ORA-01555 errors. However, the rollback segments are getting FULL frequently
ORA-1650: unable to extend rollback segment R03 by 128 in tablespace RBS
Failure to extend rollback segment 4 because of 1650 condition
FULL status of rollback segment 4 set.
and transactions are trying to use SYSTEM rollback resulting in error "ORA-01552: cannot use system rollback segment for non-system tablespace"
I am aware about solutions to this issue and upgrade to Oracle 9i to use Undo (SMU) is not immediate solution for this setup.
However I just found an article : http://www.oracledbaexpert.com/oracl...kSegments.html
Never create rollback segments using multiple datafiles within a single tablespace. Always use a single datafile or multiple tablespaces with a single datafile each. Rollback segments will not autoextend for a single transaction across multiple datafiles with the same tablespace. An attempt to extend in this fashion will simply cause Oracle to send the calling process a break (Ctl-C) and the transaction will be aborted. There should be some evidence in the alert log showing a rollback segment running out of space. This alert log evidence will not be in the form of ORA-00??? errors.
Our database has 4 datafiles of 500 MB for single rollback tablespace RBS.
Any thoughts on - if we put a single 2GB datafile for single RBS tablespace (OR create multiple RBS tablespaces with one datafile for each) will help us address the issue ?