ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Script to Monitor SMON Rollback Progress [ID 1352046.1]

Script to Monitor SMON Rollback Progress [ID 1352046.1]

原创 Linux操作系统 作者:spider0283 时间:2012-05-08 17:33:07 0 删除 编辑

In this Document
  Purpose
  Software Requirements/Prerequisites
  Configuring the Script
  Running the Script
  Caution
  Script
  Script. Output


Applies to:

Oracle Database Products > Oracle Database
Information in this document applies to any platform.

Purpose

Script. queries x$ktuxe view to check for dead transactions and repeatedly runs the query every 2 mins

Software Requirements/Prerequisites

Application : SQL

Configuring the Script

Login to sys user and run Monitor_SMON_Rollback.sql

Running the Script


SQL> connect /as sysdba
SQL> @Monitor_SMON_Rollback.sql

Caution

This script. is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this script. before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script. may not be in an executable state when you first receive it. Check over the script. to ensure that errors of this type are corrected.

Script

spool SMON_RollBack_Progress.lst
Prompt
Prompt Script. will run for 10 minutes and checks rollback status from x$ktuxe every 2 mins
Prompt -----------------------------------------------------------------------------------
Prompt
set lines 120
col useg format a30
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

Prompt
Prompt ------------------------------------------------------------------------------------

Prompt sleeping for 2 mins ....
exec dbms_lock.sleep(120);

select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

Prompt
Prompt ------------------------------------------------------------------------------------

Prompt sleeping for 2 mins ....
exec dbms_lock.sleep(120);

select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

Prompt
Prompt ------------------------------------------------------------------------------------

Prompt sleeping for 2 mins ....
exec dbms_lock.sleep(120);

select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

Prompt
Prompt ------------------------------------------------------------------------------------


Prompt sleeping for 2 mins ....
exec dbms_lock.sleep(120);

select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

Prompt ** END OF SCRIPT. **
spool off




Script. Output


SQL> @Monitor_SMON_Rolllback.sql

Script. will run for 10 minutes and checks rollback status from x$ktuxe every 2 mins
----------------------------------------------------------------------------------

Session altered.


SYSDATE USEG INSTID STATUS XID_USN XID_SLOT XID_SEQ UNDOBLOCKS
-------------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
24-aug-2011 22:18:05 _SYSSMU3_3382367247$ 0 3 3 31 153 10


-----------------------------------------------------------------------------------
sleeping for 2 mins ....

PL/SQL procedure successfully completed.


SYSDATE USEG INSTID STATUS XID_USN XID_SLOT XID_SEQ UNDOBLOCKS
-------------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
24-aug-2011 22:20:05 _SYSSMU3_3382367247$ 0 3 3 31 153 8


-----------------------------------------------------------------------------------
sleeping for 2 mins ....


SYSDATE USEG INSTID STATUS XID_USN XID_SLOT XID_SEQ UNDOBLOCKS
-------------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
24-aug-2011 22:20:05 _SYSSMU3_3382367247$ 0 3 3 31 153 6


-----------------------------------------------------------------------------------
sleeping for 2 mins ....

PL/SQL procedure successfully completed.


SYSDATE USEG INSTID STATUS XID_USN XID_SLOT XID_SEQ UNDOBLOCKS
-------------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
24-aug-2011 22:20:05 _SYSSMU3_3382367247$ 0 3 3 31 153 4


-----------------------------------------------------------------------------------
sleeping for 2 mins ....


SYSDATE USEG INSTID STATUS XID_USN XID_SLOT XID_SEQ UNDOBLOCKS
-------------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
24-aug-2011 22:20:05 _SYSSMU3_3382367247$ 0 3 3 31 153 2


** END OF SCRIPT. **

Back to TopBack to Top

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/38267/viewspace-723068/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    623955