Script to Monitor SMON Rollback Progress [ID 1352046.1]

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. **

Attachments

• Script. to Monitor Transaction Rollback Progress(2.28 KB)

Related

Products

• Oracle Database Products > Oracle Database

Keywords

ROLLBACK; SMON; TRANSACTION; UNDO

Back to Top

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

转载于:http://blog.itpub.net/38267/viewspace-723068/