Re: "Delete From <table>" taking all day

esha1_at_cix.compulink.co.uk ("Mike Ziemann East Su") wrote:

>Hi, I came across the fetch across commits causing Error 1555 too, so I
>decided to create a very large ROLLBACK segment specially for large
>deletes, when I tried to delete a 70Mbyte table Oracle reported ROllback
>segment to small even though it had extended to over 160Mb in its own
>tablespace !
>
>How big is the rollback segment supposed to be ? Why does it have to bve
>bigger that the whole table I tried to delete ?
>
>Thanks,
>
>Mike

It stores both a before and after image of every row changed. That means it will take up at least twice the size of your table, plus overhead.

If you just want to blow away all the records and aren't concerned about rolling back the delete later, use TRUNCATE TABLE instead. It uses no rollback space and returns almost instantly.

--
Chuck Hamilton
chuckh_at_dvol.com

Never share a foxhole with anyone braver than yourself!