Problems with Rollback Segments for Oracle Version 6

I am quite puzzled by the way that Oracle distributes transactions across rollback segments. In my INIT.ORA, I acquired the following rollback segments during startup:

RollBack Segment         TableSpace          MaxExtents    FileID
----------------         ----------          ----------    ------
SYSTEM_RS                SYSTEM                 30           1
USER_RS1                 USER_ROLLBACK          50           2
USER_RS2                 USER_ROLLBACK          50           3

I ran an import job (with COMMIT=Y) to populate a table of 40k records. The job failed due to failing to allocate more extent (30 extent) on FileID 1. Both DBA_EXTENTS/DBA_SEGMENTS did indicate that all 30 extents were used up. I dropped the table, re-created the table structure, and then re-submitted the import job. This time the job finished successfully. While the import job was running, I monitored the rollback activities in SQLDBA. To my satisfaction, Oracle was actually utilizing all 3 rollback segments. I encountered several other incidences where the first DML I submitted would fail, but if I re-issued the same DML the second time, the query would finished OK. Can somebody explain to me why the first DML always failed to distribute transactions to the fallback segments (e.g., USER_RS1/RS2), while the second DML managed to do so? How can I have more control over the the behavior of the rollback segments? I know I can probably remove SYSTEM_RS from the INIT.ORA, so Oracle will only be using USER_RS1/USER_RS2 which have more extents to work with. But really would like to know more about this mystery. Please help. Thanks.

Please route your answers to my E-mail account. Thanks. Received on Mon May 09 1994 - 23:09:32 CEST