Re: Problems Empty'in Tables

Mark Powell wrote in message
<01bd46c4$7f4b0840$e78177c2_at_exhibit.u-net.com>...
>Just trying to empty a table and get the following error message :-
>SQL> delete unetuser.e_cust;
>delete unetuser.e_cust
> *
>ERROR at line 1:
>ORA-01562: failed to extend rollback segment number 4
>ORA-01650: unable to extend rollback segment R03 by 64 in tablespace RBS

The table may be empty, but Oracle does not know it. It still needs to go thru every single block that was allocated to that table and check for any rows to delete. If the table's extents are sizable, this can be a problem. I usually just do a TRUNCATE on a large table as it does not require rollbacks.

The times where we have to do deletes on a such a large table <besides cursing the DBA ;-)>, we create a large temporary rollback segment, set the transaction to it and pray it will go thru. Mostly we also need to split the delete up into chucks and only do x number of rows at a time.

Most times I would not recommend increasing the existing rollback segments as that is only treating the symptom and not the problem. What happens next time if the rollback segment is too small - increase it again and again? Rather find alternative ways to handle these exceptions that requires large volume deletes.

regards,
Billy Received on Wed Mar 04 1998 - 00:00:00 CST