Re: Oracle rollback segment

"Anshumn" <anshumn.sagar_at_wipro.com> wrote in message news:375b211e.0106020142.1b1ec387_at_posting.google.com...
> Hi,
>
> Need some input regarding Oracle Rollback Segment.
>
> Due to the Rollback segment size restriction, the deletion of the
> records is failing. Now one way to solve this is to increase the size
> of
> the rollback segment.
> The other way is to use 'TRUNCATE' to delete the records. But in my
> case, I want to delete specific records based on the specified
> condition. So can some condition be used with the TRUNCATE clause? I
> checked it up, it is not allowing...any other way to do it ?

Correct "TRUNCATE TABLE WHERE ..." is not valid.
>
> If I don't increase the size of the segment, then I want to delete
> records and do an intermediate commit after every 5000 records. Any
> suggestions how to do it ?

PL/SQL
>
> Also please let me know how to increase the size of the rollback
> segment.(This is the last option)

If this is a regular operation then probably your best bet is to create a single larger rollback segment, as with all segments size is controlled by the storage clause for the CREATE statement. then when it comes to doing your batch delete first issue SET TRANSACTION USE ROLLBACK SEGMENT BIGROLL (or whatever you called it). NB Check the set transaction statement in the docs this is from memory.

--

Niall Litchfield
Oracle DBA
Audit Commission UK