Re: Rollback segments

"Lun Wing San (Oracle)" <wslun_at_qrcsun.qrc.org> writes:

>Kochis wrote:
>>
>> Trying to delete all the data from a table (6000 records)
>> I recieved the following:
>>
>> Ora - 1562: Failed to extend rollback segments (Id=1)
>> Ora - 1682: Max # extents (121) reached for rollback segment add_rb
>>
>> Did I run out of extents and I need to increase max?
>> Did I run out of space in the rollback segment? (same as above?)
>>
>> Also,
>> I have three rollback segments created and online. Oracle always seems to
>> use the first one i created? Should I point it to a different one?
 

> For the errors, it was because the size of rollback segment is not large enough. If
>121 is really the limit of your operating system, you need to drop the rollback segment
>and create a new one with a larger INITIAL storage parameter. On the other hand, if 121
>is not the limit, you can just alter table storage (MAXEXTENT upper_limit).
 

> Alternatively, if you have a large size rollback segment, you can specify to use it
>for your transaction as SET TRANSACTION USE ROLLBACK SEGMENT larger_rollback_segment at
>the beginning of your transaction. Rollback segment is used in round-robin fashion-like.

If you are deleting all the rows from the table, consider if you need to rollback the delete statement. If you do not need to rollback the deltee statement, use the truncate command. To delete all rows from table current_sales, with no pland to undo (rollback) the delte, use the following command:

TRUNCATE TABLE current_sales; Received on Fri Dec 13 1996 - 00:00:00 CST