Re: How much rollback left to apply?

Hi Kyle,

Once we ran into similar situation when we bounced our database in the middle of a long running transaction.

Metalink Note: 414242.1 was helpful in resolving this issue.

Hope it helps you solve your issue as well. Regards

Asif Momen
http://momendba.blogspot.com

kyle Hailey <kylelf_at_gmail.com> wrote: Is there a way to see how much rollback is left to apply? Currently SMON seems to be busily applying rollback. I can see SMON reading both the UNDO and then the tablespace that contains a 2 gig table which had a big delete statement (no commit until the end) running and then canceled yesterday. The statistic "rollback changes - undo records applied" is ever increasing for SMON. There are no entries in v$transaction. The database has been restarted since the delete statement was canceled.
Doing further deletes on the table produces lots of enqueue TX 4 locks waiting for SMON. I can create a copy of the table no problem and then do deletes on the copy with out any lock contention. It seems that it would be much more efficient just to recreate the table and drop the original, but I'm curious if there is a way to see how long the SMON cleanup will take.
The delete was canceled yesterday and SMON is still applying UNDO 24 hours later. I'm curious if I can see how much more work SMON has to do to complete the application of rollback. It's interesting that I can create a copy of the table but not do deletes on it.Acutally the deletes work but after waiting for a TX 4 enqueue on the order of a second or two. The table has 3 indexes on it which I can see also involved in the rollback applied by SMON (that's logical). SMON has been pegged for as long as I have history ( a few hours) at almost 100% IO wait (little bit of CPU) reading the table, UNDO and indexes block by block (sequential reads).
--

http://www.freelists.org/webpage/oracle-l