There is no way to disable rollback segments! What is happening is that each
deleted row, as it is being deleted, is being replicated in the rollback
segment, in case your transaction fails or you would otherwise request to
rollback your delete. Here are some options:
- If all rows are deleted daily from the table, and you are sure you want ALL
rows to be deleted without the chance to roll back this transaction, use the
TRUNCATE TABLE statement. It does not use the RS, but again, all rows are
definately gone once this staement is run. Note that it is VERY VERY fast, and
is best if an entire table is to be "wiped out" without droppping and
re-creating the table.
- You could write a PL SQL or embedded SQL in a C or other 3GL programming
language to delete rows in sets of say 50,000 rows at a time. Set the
transaction, count the number of deletes one row at a time, commit when you hit
your chosen number (if 50,000 works, great if not choose a smaller number), and
then start another transaction and repeat sets of deletes until there are no
more rows. If all rows are not to be deleted, this method can be used to set
other criterion on which rows to delete and which are not to be deleted.
- Make your RS bigger, try agin, make it bigger, try again...at the risk of
running out of disk space. I think Oracle 8.0 allows unlimited extents of
rollback segments, but again, you risk running out ofspace, and you aren't at
Oracle 8.0
Received on Sat Nov 29 1997 - 00:00:00 CST
