Re: Long Delay before rolling back

Roger,
What version of Oracle? How is the kill being done?

I wrote an FAQ on identifying and estimating transaction rollback time a few years back.

Is there a way to detect processes that are rolling back, and can I figure out how long it will take ?
http://www.jlcomp.demon.co.uk/faq/rolling_back.html

I have always done the join between v$session s and v$transaction t be on
 t.addr =s.taddr but it would appear that Oracle added or exposed a pointer back from v$transaction. I will have to look into that.

I have seen where Oracle does not seem to recognize that it should be rolling back. It may be that the data has been flushed from the buffer to disk and Oracle is going to allow the first session to access the data to perform the rollback. Logically the rdbms should perform the rollback within a reasonable period even if no user session accessed it.

If the problem reoccurs try adding the following lines to your code or the code in the FAQ. This should identify if Oracle has flagged the transaction as being in a rolling back state.

,case when bitand(t.flag,power(2,7)) > 0 then 'RB in Progress'

                                          else 'Not Rolling Back'
                                          end as "F Status"

Then if the session is still just setting there after x minutes try accessing a few of the blocks affected by the failed transaction and see if this triggers any kind of change by the rdbms.

If nothing else this should provide you more information to add to an iTAR.

HTH -- Mark D Powell -- Received on Thu Feb 03 2005 - 08:43:03 CST