oracle按照时间点回退,【Oracle】查看事务回滚的时间

首先，我是做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

这里主要说下一个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 

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还在未响应中。。。