information on transactions being rolled back? 1

[braxton]

I'm using OEM to monitor running processes and transactions, but I'm wondering how to get status on transactions that are currently being rolled back? For example:
1) user submits massive query inserting millions of rows
2) I view the session from OEM, observe that the estimated time of completion is in 27 hours, and tell the user to kill it.
3) user kills it from sqlplus
4) the session stays around. this is to be expected while the transaction is being rolled back.

During step 4, is there any way to get the estimated time of completion for the rollback?

Thanks,
Braxton

 

[braxton]

So I did some investigation, and I came up with a kludge.
set your nls_date_format to display minutes and seconds, then run:

select sysdate, log_io, phy_io, used_ublk, used_urec from v$transaction;
wait a while, and run it again. It seems that when rolling back a transaction, used_urec (the number of records held by the transaction) decreases. Thus I did some rough timing by calculating:

(used_urec2/(used_urec1-used_urec2))*(sysdate2-sysdate1)

i.e. the number of rows per second is (used_urec1-used_urec2)/(sysdate2-sysdate1), and dividing total rows by the number of rows/second gives a rough estimate of total seconds remaining.

Is there a better way to do this? Just curious.

 

[karluk]

Your way looks pretty good to me.