VMCD.ORG

Focus on DB architect mail:ylouis83#gmail.com

Scripts: Monitor smon rollback progress

Posted by admin on July 7th, 2012

this scripts is used to monitor transaction rolling back progress

-------------------------------------------------------------------------------
--
-- Script: rolling_back.sql
-- Purpose: to predict when transactions will finish rolling back
-- For: 9.0+
--
-- Copyright: (c) Ixora Pty Ltd
-- Author: Steve Adams
--
-------------------------------------------------------------------------------
spool save_sqlplus_settings

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;   
/

prompt
exit;

eg:

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 7 14:54:02 2012
 
Copyright (c) 1982, 2011, OracleAll rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> @rollback
 
Looking for transactions that are rolling back ...
 
SYS's transaction 10.3.25785 will finish rolling back at approximately 14:55:52
07-JUL-2012
SQL> begin
 
2  for i in 1..1000000 loop
 
3  insert into t1 values (i);
 
4  end loop;
 
5  end;
 
6  /
 
PL/SQL procedure successfully completed.
 
SQL> rollback
 
2  ;
 
Rollback complete.
 
SQL> !date
Sat Jul  7 14:55:59 CST 2012

非常准确的时间估算 cool scripts :)

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>