!HELP! Rollback logs preventing deletes.
Thomas Kyte
(if that email address didn't require changing)
On Fri, 27 Aug 1999 18:56:23 GMT, you wrote:
>Hi,
>
>I have a schema with intense FKY usage and lot's of ON DELETE CASCADE
>enabled constraints. I am anticipating deletes to routinely result in
>the (cascaded) deletion of 500,000+ records. These sort of transactions
>will be processed frequently. (Records will be created in numbers
>orders of magnitude greater than this, hourly.) Currently, the database
>(7.3.4.4.1) is giving these errors in response to delete attempts...
>
>ORA-01562: failed to extend rollback segment number 3
>ORA-01650: unable to extend rollback segment ROLL3 by 512 in tablespace
>RBS
>
>I do not need to be able to back out of the delete transaction ever,
sure you do -- lots of times and in lots of cases. consider what happens when
the power goes out in the middle of your delete. you would have a completely
corrupt database if we didn't rollback that partially done, incomplete work.
consider what happens when the client issueing the delete 'goes away' (reboots).
it is also how we provide for non-blocking queries and consistent reads -- if we
didn't have the rollback, lots of queries against the tables being deleted would
return the wrong answer or just sit blocked for long periods of time.
>though I do need the ability to ROLLBACK during record creation. How
>can I get the best of these two worlds without taking over all of the
>resources of the HP that holds my database along with other projects'
>databases?
>
You'll need to supply sufficient rollback segments to perform your transactions.
Since the entire transaction will use but one rollback segment, perhaps you can
cut down on the number of rollback segments and create few, larger rollback
segments. Rollback is a very important part of the database, it must be sized
to perform the work you want to do.
>Thanks ahead of time!
>
>Martin Douglas
>Phantom Works
>The Boeing Company
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tk...@us.oracle.com
Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation