Re: Use of rollback segments

From: <beansboy_at_my-dejanews.com>
Date: Mon, 24 Aug 1998 19:30:56 GMT
Message-ID: <6rsf1g$hed$1@nnrp1.dejanews.com>

In article <35dc2b22.0_at_d2o19.telia.com>, "Johan Nilsson" <jni_at_esrange.ssc.se.---> wrote:

>
> If you want to delete 515000 rows you've got to have rollback segment(s) big
> enough to keep all data you wish to delete (a bit cryptic, sorry). On the
> other hand, if you want to delete ALL data from a table there is a specific
> command to do that which doesn't use rollback segment (truncate, I believe).
>
> H.T.H. // Johan
>

Yup - you can cause Oracle to bypass using rollback segments if you use the 'truncate' command, ie 'truncate table emps;'. This immediately removes all records from said table, and it auto-COMMITs it (you cannot issue a ROLLBACK to get your records back). I believe that you need to be the table's owner to do so, though.

Of course, the other way to get around (a) needing to have very large transactions, but (b) not creating more rollback segments, is to put a loop into a procedure & commit after every x records (instead of making the entire DELETE transaction as a single transaction).

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Aug 24 1998 - 14:30:56 CDT