首先，我是做BI的，在工作中偶尔会出现一些特殊情况，比如突然在某天ETL日增量调度的时候一个简单的插入操作卡住不动几小时都过不去，平时可能30分钟左右。
可能是资源征用导致DataStage资源分配不均等各种原因，这个时候通常会有运维的同事协助中断此作业的运行，进行重新插入。

中断后就要开始漫长的等待，等全部rollback后进行rerun这个JOB。

而这时候业务用户就不爽了，为什么都9点了我还看不了报表数据？通常情况我们会先各种赔礼道歉然后给一个大概的时间点安抚用户，
等待时间基本就是回滚时间 + 重新插数时间，插数时间可以从调度日志中获取历史平均值，而回滚时间就难以获取，这里给大家普及一个查看事物回滚时间的知识点

这里我们不详细讲解DB异常关闭的SMON事务回滚，大概说下：
基本分3种方式：
1. 查询视图V$FAST_START_TRANSACTIONS中字段UNDOBLOCKSDONE，UNDOBLOCKSTOTAL估算smon恢复进度
2. 跟踪日志
3. 查询内部试图x$ktuxe [k]ernel layer [t]ransaction layer [u]ndo transaction [e]ntry
可参考http://www.ssc.stn.sh.cn/html/zsk/ITyw/2012-04/5819.html

这里主要说下一个insert、delete、update等语句的rollback时间估算
建个测试表

点击(此处)折叠或打开

create table table_chenxu as select * from dba_objects

插入大量数据

点击(此处)折叠或打开

begin
for i in 1..100
loop
insert into table_chenxu
select * from dba_objects;
commit;
end loop;
end;

首先第一步我们要拿到正在回滚的SQL的SQLID和SESSIONID，这个简单，假如我们对table_chenxu进行delete，回滚过程中这个table_chenxu必定是被锁的，所以我们可以根据gv$locked_object来查看当前对象是否被锁，且根据sid查看锁表的SQL

我们先update下这个表
update table_chenxu t set t.owner = 'azzo';

然后查询被锁的相关信息：
注：多个系统视图关联查询的时候为了防止试图合并可以加上use_hash或者no_merge

点击(此处)折叠或打开

select /*+ use_hash(o,s,t,q)*/
t.object_name, o.session_id, s.sql_id, q.sql_text, s.*
from gv$locked_object o, dba_objects t, gv$session s, gv$sql q
where t.object_id = o.object_id
and o.session_id = s.sid
and s.sql_id = q.sql_id
and t.object_name = 'TABLE_CHENXU'

结果如下：

我是PC机，update用了628秒，现在rollback（plsql developer死了。。于是重新开一个）：

使用如下SQL查看ktuxesiz字段，过一段时间（如10s）再执行一次，再看一下，两个结果相减除以10就是每秒回滚的速度

点击(此处)折叠或打开

select
s.username,
t.xidusn,
t.xidslot,
t.xidsqn,
x.ktuxesiz
from
sys.x$ktuxe x,
sys.v_$transaction t,
sys.v_$session s
where
x.inst_id = userenv('Instance') and
x.ktuxesta = 'ACTIVE' and
x.ktuxesiz > 1 and
t.xidusn = x.ktuxeusn and
t.xidslot = x.ktuxeslt and
t.xidsqn = x.ktuxesqn and
s.saddr = t.ses_addr;

或者使用自动化脚本（取自网络）

点击(此处)折叠或打开

declare
cursor tx is
select
s.username,
t.xidusn,
t.xidslot,
t.xidsqn,
x.ktuxesiz
from
sys.x$ktuxe x,
sys.v_$transaction t,
sys.v_$session s
where
x.inst_id = userenv('Instance') and
x.ktuxesta = 'ACTIVE' and
x.ktuxesiz > 1 and
t.xidusn = x.ktuxeusn and
t.xidslot = x.ktuxeslt and
t.xidsqn = x.ktuxesqn and
s.saddr = t.ses_addr;
user_name varchar2(30);
xid_usn number;
xid_slot number;
xid_sqn number;
used_ublk1 number;
used_ublk2 number;
begin
open tx;
loop
fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
exit when tx%notfound;
if tx%rowcount = 1
then
sys.dbms_lock.sleep(10);
end if;
select
sum(ktuxesiz)
into
used_ublk2
from
sys.x$ktuxe
where
inst_id = userenv('Instance') and
ktuxeusn = xid_usn and
ktuxeslt = xid_slot and
ktuxesqn = xid_sqn and
ktuxesta = 'ACTIVE';
if used_ublk2 < used_ublk1
then
sys.dbms_output.put_line(
user_name ||
'''s transaction ' ||
xid_usn || '.' ||
xid_slot || '.' ||
xid_sqn ||
' will finish rolling back at approximately ' ||
to_char(
sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,
'HH24:MI:SS DD-MON-YYYY'
)
);
end if;
end loop;
if user_name is null
then
sys.dbms_output.put_line('No transactions appear to be rolling back.');
end if;
end;

结果如下：
SCOTT's transaction 8.19.1171 will finish rolling back at approximately 19:47:57 07-9月 -2015

是不是很方便~ 不过自动化脚本需要有dba权限哦，sys.dbms_lock.sleep(10)这个包，普通用户是没权限以调用的，如果没有dba权限就掐表及时吧

直到写完这篇文章，我的plsql还在未响应中。。。

ITPub博客

【Oracle】查看事务回滚的时间

最新文章