Re: !HELP! Rollback logs preventing deletes.

Martin Douglas 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,
> 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?

When I have recevied a message like that it has normally indicated that there isn't sufficient space in the tablespace for the rollback segment to extend to MAXEXTENTS.
What you may want to try (providing you have the disk space) is turn autoextend on for the tablespace where your rollback segments are located and then try the transaction. If you then get a message indicating that you've reached maxextents then double your NEXT size and try again and keep doing that until the transactions can complete successfully.

Ken Received on Mon Aug 30 1999 - 07:05:01 CDT