Re: !HELP! Rollback logs preventing deletes.

A copy of this was sent to Martin Douglas <Martin.Douglas_at_Boeing.com> (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                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Aug 28 1999 - 09:31:27 CDT