How to tell if and how long rollback will take

From: Peter <peter_at_peter.com.noaddress>
Date: Thu, 20 Feb 2003 10:56:09 +1000
Message-ID: <b31942$vo1$1@bunyip.cc.uq.edu.au>

I found this query that shows the number of blocks that need to be rolled back. It is quite handy when a transaction has bombed out and you don't know how long it will take for oracle to rollback. I found the answer on metalink with the key phrase "When Rollback Finishes"

select

 a.sid,
 a.username,
 b.xidusn rollback_seg_no,
 b.used_urec undo_records,
 b.used_ublk undo_blocks

from
 v$session a,
 v$transaction b
where
 a.saddr=b.ses_addr;

Please add any other useful hints or tips if you have any. Received on Wed Feb 19 2003 - 18:56:09 CST