We have been receiving the following error messages in our alert log:
ORA-1650 unable to extend rollback segment BIG_ROLLBACK by 640 in tablespace RBSBIG
Failure to extend rollback segment 2 because of 1650 condition
v$rollstat info on segment #2 is:
extents rssize optsize curext shrink wrap extends
139 728752128 20971520 138 19 332 311
initial extent and next extent are both 5242880.
So there are basically 3 options: have the developer write more commits in the program, defrag the RBS tablespace (how can I tell if its defragmented?), or add more size to the RBS tablespace. But how do I know which is the best option?
I really don't know exactly what else I need to check/monitor with this rollback segment. Does anybody have any suggestions?
ORA 1650 indicates that there is not enough space for rollback segment to extend. Add more space to the rollback tablespace.
You have optimal set on the segment and it will shrink. Its just a query which needs a large rollback segment. Add more space for now and in the meantime you can ask the developers to optimize the query.
Your situation looks like a batch processing work. If no. of commits can be increased in the apllication that can be better.
If you are running more than one batch work(transaction) at a time, why don't you look at an option like creating another rollback segment and force the other transaction to the new rollback segment using the "Set tansaction use rollback segment ..." command.