Oracle view the transaction script using the UNDO segment

For more information about Undo tablespace management, see Oracle undo tablespace management.

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

1. /* FormattedOn18:10:55 (QP5 v5.185.11230.41888 )*/
2. SELECTT1.USN,
3. T2.NAME,
4. T1.STATUS,
5. T1.LATCH,
6. T1.EXTENTS,
7. T1.WRAPS,
8. T1.EXTENDS
9. FROMV $ ROLLSTAT T1, V $ ROLLNAME T2
10.  WHERET1.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

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:

1. /* FormattedOn13:28:55 (QP5 v5.185.11230.41888 )*/
2. SELECTS. SID,
3. S. USERNAME,
4. U.NAME,
5. Q. SQL _TEXT,
6. Q. HASH_VALUE,
7. T. UBABLK
8. FROMV $TRANSACTIONT,
9. V $ rollstat r,
10. V $ rollname u,
11. V $ session s,
12. V $ SQL Q
13. WHERES. TADDR = T. ADDR
14. ANDT. XIDUSN = R. USN
15. ANDR. USN = U. USN
16. ANDQ. HASH_VALUE =
17. DECODE (S. SQL _HASH_VALUE,
18. NULL, S. PREV_HASH_VALUE,
19. S. SQL _HASH_VALUE)
20. ORDER BYS. USERNAME;