RE: How far I am in my delete ?

> jankovi,
>

i guess you could look at "rows_processed" column of v$sqlarea for the delete stmt issued.

Rahul

> ----------
> From: Steven Monaghan[SMTP:Steve.Monaghan_at_wcom.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: 13 Juni 2000 06:05
> To: Multiple recipients of list ORACLE-L
> Subject: 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
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Author: Steven Monaghan
> INET: Steve.Monaghan_at_wcom.com
>
> 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 Tue Jun 13 2000 - 04:04:22 CDT