RE: How far I am in my delete ?

This doesn't answer any of your questions, but could provide an alternative that does not require downtime.

1 - create the temp table with the same structure 2 - build an insert trigger on the live table that also puts the data in the temp table

3 - enable the trigger
4 - copy the data you want to save from the live table to the temp table
5 - truncate the live table (thus resetting the HWM)
6 - copy the data back from temp into live
7 - drop temp table

We use this because it requires no down time for the table, as well as prevents forgetting things (like FKs, synonyms and grants).

Steve Monaghan
Worldcom
Long Island, NY, USA

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Djordje
> Jankovic
> Sent: Monday, June 12, 2000 3:08 PM
> To: Multiple recipients of list ORACLE-L
> Subject: How far I am in my delete ?
>
>
> Hi friends,
>
> Few nights ago I had an interesting experience.
> I was deleting 90% of rows from a decently large table (300M, 5.5M rows).
> At the same time the table was being constantly inserted (approx.
> 8 rows per
> minute). After few hours, I started wondering when it is going to be
> finished. Rollback space kept increasing and increasing, as well as the
> number of redo log files generated.
> To make the long story short I had to stop the process (it was
> slowing down
> the other stuff, and the start of a busy day was close) after
> five hours of
> deletes, and around 1G of rollback space taken. The rollback itself took
> almost another four hours.
> I learned my lesson - never do massive deletes if you do not
> have to. Next
> time I will copy the records to another table, drop the existing
> one, rename
> the new one. The problem with that is that I will have to stop
> inserts, but
> I am not going through the same ordeal again.
> Questions for you gurus:
> 1. Is there a way to find out how far a delete has gone. I was looking
> at statistics from v$sesstat but could not find anything indicative.
> 2. How would you estimate the amount of rollback that is going to be
> generated in an operation. In my case deleting 270M form a 300M
> would have
> generated more than 1G of logs - I expected that those numbers should be
> comparable, and definitely not 1:3 or more.
> 3. Was the relation between rollback and operation time, 4:5, for a
> delete a typical one. Similar question to the previous one - can one
> estimate how long a rollback operation would last ?
> 4. I was watching processes while the rollback was on. Earlier I was
> under the impression that the rollback would be performed by PMON, but it
> seemed to me that it was done by the server process itself (it was in
> "marked to kill" state) - with the help of DBWR and LGWR, but PMON was not
> involved. Is that true ? What would happen if that server process was
> terminated on the unix side - who and how would end the rollback ?
> 5. One last question: if I had to shutdown the instance abort while it
> was rolling back, what would happen when I restart it. Would the database
> be opened for all the users, while that process would is being rolled back
> in parallel (as far as I remember this is what I learned from a
> manual), or
> oracle would oracle wait to rollback the transaction and only
> than open the
> database ?
>
>
> Thanks for all your time, but I thought those questions may be of interest
> to other people too.
> Djordje
> --
> Author: Djordje Jankovic
> INET: djankovic_at_corp.attcanada.ca
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Mon Jun 12 2000 - 16:56:45 CDT