Re: commit afer unable to extend rollback segment

Oracle Server Concepts Transaction Management states:

If at any time during execution a SQL statement causes an error, all effects of the statement are rolled back. The effect of the rollback is as if that statement were never executed.

Errors that cause statement rollbacks are errors discovered during the execution stage of SQL statement processing (such as attempting to insert a duplicate value in a primary key or an invalid number into a numeric column), not the parsing stage (such as syntax errors in a SQL statement). Single SQL statements can also be rolled back to resolve deadlocks (competition for the same data); see "Deadlock Detection" .

Therefore, a SQL statement that fails causes the loss only of any work it would have performed itself; it does not cause the loss of any work that preceded it in
the current transaction.

joseph_sumalbag_at_bose.com wrote:
>
> No Sasha ,
> once the Oracle server encounters an error , it will immediately
> rollback the entire transaction .
>
> What I suggest for you to do is use the s
> "Set transaction use rollback
> segment BIGROLLBACKSEGMENTNAME"
>
> to use the larger rollback segment in your DB for your
> transaction, or maybe do a pl/sql procedure to break the
> transaction into smaller pieces .
>
> I hope this helps !
>
> Sasha Ostojic wrote:
> >
> > If I do "DELETE FROM my_table", and it eventually comes back with an error
> > saying "unable to extend rollback segment", is it OK to do a COMMIT? Will
> > the COMMIT purge any of the records that made it into the rollback segment
> > before it filled up? Or am I asking for trouble if I do this?
> >
> > I would prefere to do this rather than extend the rollback segment because
> > it is a one-time thing.
> >
> > Thanks,
> > .sasha
Received on Tue Oct 21 1997 - 00:00:00 CDT