The SET TRANSACTION USE ROLLBACK SEGMENT .... statement is valid only for one
transaction only. Once you COMMIT a transaction and then start a new one, you will
have to use that statement again; otherwise, ORACLE chooses a rollback segment for
the next transaction randomly.
I would suggest that you do the following:
- Shutdown the ORACLE instance.
- Modify the INIT.ORA file as follows:
- comment out the ROLLBACK_SEGMENTS entry and add a new one: ROLLBACK_SEGMENTS=R08
- comment out the TRANSACTIONS_PER_ROLLBACK_SEGMENT and add a new one with a
value so that the ratio of the TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT<=1
(You can change both or either parameter). This last change is required to make sure
that ORACLE brings online only one rollback segment. On instance startup, ORACLE
attempts to bring online a number of rollback segments at least equal to the value
of the above ratio from the pool of defined rollback segments, even when they are
not listed in the INIT.ORA file.
- Startup the instance and open the database.
- Make sure that only the SYSTEM rollback segment and R08 are online (SELECT
segment_name, status FROM dba_rollback_segs).
- If everythings pans out, run your query, and good luck (cross your fingers, too,
just to make sure !).
Hope this helps.
Michael Serbanescu
dtang_at_minn.net wrote:
>
> Hi:
>
> I got a very serious problem. I have a huge query to update a large
> table. It runs several hours, then dead with oraerr 01555. Snapshot
> too old, the rollback segment R05 is too samll.
> Then I creat a large rollbak segment O08, then
> use set Transaction use rollback segment r08, and run that query
> again.
> After hours , it died again with same error 01555, but it still say:
> "roll segment Ror (or whatever other than R08) is too small".
>
> I don't understand why the engine doesn't usr rollback segment I
> specified. Does anyone has any idea.
>
> If anyone has experience running a large query, could you help me put
> on this one. My email address is dtang_at_minn.net
>
> Thanks ahead
>
> Dong
Received on Wed Mar 25 1998 - 00:00:00 CST
