Re: Delete Rows without Rollback ?

I have seen this problem a number of times and a work arround (if you have the space and no concurrent access) is :

create table copytable as select .... NOLOGGING (or UNRECOVERABLE in Oracle7)
drop table tablename
rename copytable to tablename

Regards
David Russell

Jason Archambeau wrote in message
<01bdc928$9b2c79e0$773537a6_at_GATOR.internetMCI.COM>...
>There may be another option as well,
>
>you could create a very large rollback segment and use the SET TRANSACTION
>statement to tell your delete to use this rollback segment. This will
>eliminate any 'SNAPSHOT TOO OLD' errors provided you size the new rollback
>segment large enough.
>
>Allan Nelson <nels212_at_austin360.com> wrote in article
><35D6EB7B.77530AE9_at_austin360.com>...
>> John Finn wrote:
>>
>> > Does anyone know how to "DELETE FROM TABLE WHERE ..." without the
>> > rollback overhead? If I have 1000000 rows and I want to delete 50% of
>> > them without posibility of a rollback and reduced transaction overhead,
>> > is there a way to do this?
>> >
>> > Thanks
>> > jfinn
>> >
>> > pls reply to group only.
>>
>> You won't be able to do this. Oracle will always post the deletes to
>> rollback segments to permit transaction rollback. The best I think you
>> can do, if snapshot too old messages are a problem, is to select the row
>> id's in question into a PL/SQL data structure and then commit between
>> appropriate groups of deletes. This will keep your active rollback
>> segment usage down, but it will not prevent the change records from each
>> delete being written in the first place.
>>
>> Another way to do it if you are really determined to do this without
>using
>> rollback segments is to dump the table to an ascii file, truncate the
>> table, do the deletes on the OS and then import the data back in with the
>> fast path loading options.
>>
>> Allan
>>
>>
Received on Sun Aug 16 1998 - 15:43:21 CDT