Oracle view the transaction script using the Undo segment

For undo tablespace management instructions, refer:

Oracle undo tablespace Management

Http://blog.csdn.net/tianlesoftware/article/details/5689558

We can use the following script to view the information of the Oracle undo Segment segment:

/* Formatted on 2012/6/18 18:10:55 (QP5 v5.185.11230.41888) */SELECT T1.USN,       T2.NAME,       T1.STATUS,       T1.LATCH,       T1.EXTENTS,       T1.WRAPS,       T1.EXTENDS  FROM V$ROLLSTAT T1, V$ROLLNAME T2 WHERE T1.USN = T2.USN;

 

 

When the Undo tablespace fails, we need to pay attention to the Undo segment information:

How to deal with currentonline redo and undo corruption

Http://blog.csdn.net/tianlesoftware/article/details/6261475

 

Combined with the V $ session and V $ transaction views, you can confirm that each transaction uses the Undo segment. When the Undo tablespace is abnormal, you can use the following script to check whether the transaction uses the Undo segment:

/* Formatted on 2012/6/18 13:28:55 (QP5 v5.185.11230.41888) */  SELECT S.SID,         S.USERNAME,         U.NAME,         Q.SQL_TEXT,         Q.HASH_VALUE,         T.UBABLK    FROM V$TRANSACTION T,         V$ROLLSTAT R,         V$ROLLNAME U,         V$SESSION S,         V$SQL Q   WHERE     S.TADDR = T.ADDR         AND T.XIDUSN = R.USN         AND R.USN = U.USN         AND Q.HASH_VALUE =                DECODE(S.SQL_HASH_VALUE,                        NULL, S.PREV_HASH_VALUE,                        S.SQL_HASH_VALUE)ORDER BY S.USERNAME;

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940