Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Delete Rows without Rollback ?

Re: Delete Rows without Rollback ?

From: David <darussell_at_msn.com>
Date: Sun, 16 Aug 1998 21:43:21 +0100
Message-ID: <#qoKYaVy9GA.301@upnetnews05>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US