我们在大的事务失败时往往面临长时间的回滚,在回滚期间表会被加以TM-3 SX sub-exclusive锁,此时一般我们是无法针对表实施DDL操作的。长时间的大事务回滚可能耗尽我们的耐心,不过我们还是有办法预估何时回滚能够完成的,参考中的脚本
<Script:when transaction will finish rollback>中的脚本,注意该脚本需要访问x$ktuxe内部视图,所以需要以sysdba身份方能执行。
SQL> select * from v$lock where type in ('TM','TX'); ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 0ED0F30C 0ED0F33C 9 TM 13865 0 3 0 3757 0 2C3975FC 2C39763C 9 TX 65557 677 6 0 3757 0 SQL> select object_name,object_type from dba_objects where object_id=13865; OBJECT_NAM OBJECT_TYPE ---------- ------------------- SAMPLE TABLE 因为表上存在TM-3锁,所以此时是无法对表执行需要持有TM-6 exclusive排它锁的DDL操作的 SQL> drop table sample; drop table sample * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired SQL> select * from sample where rownum=1 for update nowait; select * from sample where rownum=1 for update nowait * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired rollback完成之前相关行上的row level lock不会被释放,因此dml操作会被block SQL> select xidusn,xidslot,xidsqn,status from v$transaction; XIDUSN XIDSLOT XIDSQN STATUS ---------- ---------- ---------- ---------------- 1 21 677 ACTIVE 从v$tranasction视图中可以看到事务1.21.677处于active状态 使用set time on命令显示当前时间 SQL> set time on; 20:54:26 SQL> set serveroutput on set feedback off prompt prompt Looking for transactions that are rolling back ... prompt 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; / MACLEAN's transaction 1.21.677 will finish rolling back at approximately 21:46:45 25-MAY-2011 以上脚本给出了估计的1.21.677事务的回滚结束时间为21:46:45,即还有50分钟, 注意这只是根据之前单位时间内rollback的进度所得到的一个估算值,所以并不精确,但还是可以做为一个参考 再次运行以上脚本,会发现2次的结果不一定一致 SQL> / MACLEAN's transaction 1.21.677 will finish rolling back at approximately 21:35:47 25-MAY-2011在产品数据库中应当尽可能避免出现大事务回滚的现象,因为除去lock的影响外,large transaction rollback还会导致CPU使用率大幅上升并产生往往比事务本身所产生的更多的redo重做日志记录。
转载于:https://blog.51cto.com/maclean/1277803