In this Document
Purpose
Software Requirements/Prerequisites
Configuring the Script
Running the Script
Caution
Script
Script. Output
Applies to:
Oracle Database Products > Oracle DatabaseInformation 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 minsSoftware Requirements/Prerequisites
Application : SQLConfiguring the Script
Login to sys user and run Monitor_SMON_Rollback.sqlRunning the Script
SQL> connect /as sysdba
SQL> @Monitor_SMON_Rollback.sql
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
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. **
|
|
|
|
