Focus on database architecture

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 Looking for transactions that are rolling back ...

  cursor tx is
      sys.x$ktuxe  x,
      sys.v$transaction  t,
      sys.v$session  s
      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;
  open tx;
    fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
    exit when tx%notfound;
    if tx%rowcount = 1
    end if;
      inst_id = userenv('Instance') and
      ktuxeusn = xid_usn and
      ktuxeslt = xid_slot and
      ktuxesqn = xid_sqn and
      ktuxesta = 'ACTIVE';
    if used_ublk2 < used_ublk1
        user_name ||
        '''s transaction ' ||
        xid_usn  || '.' ||
        xid_slot || '.' ||
        xid_sqn  ||
        ' will finish rolling back at approximately ' ||
          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
    sys.dbms_output.put_line('No transactions appear to be rolling back.');
  end if;



SQL*Plus: Release Production on Sat Jul 7 14:54:02 2012
Copyright (c) 1982, 2011, OracleAll rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release - 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
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=""> <s> <strike> <strong>