Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: deleting large number of records from table...
On 11 Nov 1997 13:30:45 GMT, "Jerel McDonald" <jerel@_delete_azstarnet.com> wrote:
>Create one large rollback segment. Use the 'set transaction' command
>to use that rollback segment for your large transaction. All other
>transactions will use all rollback segments in the typical round-robin
>fashion. This way you can have many rollback segments but only
>need one large one.
>
>syntax is something like:
>set transaction use BigRollbackSegment;
>delete from table1;
>commit;
This is the method that I use to delete about 3.5 million rows from my table. I combine it with a PL/SQL loop as mentioned earlier, using 'set transaction' at the beginning of my script and after every commit.
The danger in committing too often is receiving a 'snapshot too old' message as too much redo information is being saved. I had to play with this a lot before I hit the magic number - having a 1gb rollback segment kinda helped too, although it's probably too big (oh well, disk space is cheap).
To respond, remove BITEME_SPAMMERS_ from my address