Out of transaction slots
I saw a couple of ORA-01595/ORA-01594 errors in the alert log like:
23-FEB-2003 06:01:49.74:
Errors in file $1$DGA1001:[ORACLE8.DB_CCICAD.TRACE]NICK_CCICAD_BG_SMON_006.TRC;:
ORA-01595: error freeing extent (1) of rollback segment (41))
ORA-01594: attempt to wrap into rollback segment (41) extent (1) which is being freed
I found some treads on Metalink about this and one recommendation was to increase the size of the OPTIMAL setting, another was to increase the size of the rollback segments and to make the minimum extent 10 or 20 and to make OPTIMAL = NEXT * MINEXTENTS. Following these suggestions I modified the rollback segments using the following for each public RBS:
alter rollback segment RBS00 offline;
drop rollback segment RBS00;
create public rollback segment RBS00 tablespace RBS_DATA storage
(initial 256K next 256K minextents 10 maxextents UNLIMITED optimal 2560K);
alter rollback segment RBS00 online;
Just for grins I dropped RBS40 and didn’t recreate it. I checked the alert log again and found a whole lot of:
25-FEB-2003 09:14:50.44:
Errors in file 1$DGA1001:[ORACLE8.DB_CCICAD.TRACE]NICK_CCICAD_BG_SMON_006.TRC;:
ORA-00604: error occurred at recursive SQL level 1
ORA-01554: out of transaction slots in transaction tables
I checked Metalink and found Note:18953.1 which simply stated that “There were too many concurrent transactions.” The action was to take down Oracle and modify the init parameters TRANSACTIONS and ROLLBACK_SEGMENTS and restart. Since that wasn’t an option I decided to recreate RBS40 and put it online. The ORA-00604/ORA-01554 error messages quit right away. I do have a TRANSACTIONS parameter but I don’t have a ROLLBACK_SEGMENTS parameter so I’m not sure what I did other than put the squeeze on the system and it griped! Should I have simply taken RBS40 off-line and drop it later?