Database version:
Oracle 11.2.0.3 RAC
Experimental Purpose:
View Oracle transaction information through the undo block
Experimental Details:
1 Starting a transaction
Sql> select * from T1;
ID NAME
---------- ------------------------------
1 YCR
2 Zhy
3 Wya
5 LJ
4 ZHB
2 Mk
2 cc
sql> Update T1 set id=6 where name= ' cc ';
1 row updated.
Do not commit or rollback this transaction.
2 Viewing transaction information
A) View session number:
Sql> select Sid from V$mystat where Rownum=1;
Sid
----------
27
b) obtain information such as the Undo slot:
Sql> Select Xidusn,xidslot,xidsqn from V$transaction t,v$session s where s.sid=27 and s.taddr=t.addr;
Xidusn Xidslot xidsqn
---------- ---------- ----------
10 26 1741
Sql> Select Sid,trunc (id1/65536) usn,mod (id1,65536) Slot,id2 Wrap,lmode from V$lock where type= ' TX ' and sid= ' 27 ';
SID USN SLOT WRAP lmode
---------- ---------- ---------- ---------- ----------
27 10 26) 1741 6
which
Column Description
XIDUSN Undo Segment Number
Xidslot Slot Number
XIDSQN Sequence Number
3 Find the Undo block based on the information you have queried:
A) View Undo header Location
Sql> Clear Columns
Columns cleared
Sql> col name for A40
Sql> SELECT * from V$rollname where usn=10;
USN NAME
---------- ----------------------------------------
Ten _syssmu10_3271578125$
b) Dump undo block
Alter system dump Undo Header "_syssmu10_3271578125$";
SELECT * from V$diag_info where name= ' Default Trace File ';
/u01/app/oracle/diag/rdbms/irac/irac1/trace/irac1_ora_4444.trc
c) View the Undo header information:
Index state cflags wrap# uel SCN dba parent-xid NUB stmt_num CMT
------------------------------------------------------------------------------------------------
0x19 9 0x00 0x06d0 0x0012 0x0000.00b6ed00 0x00c00382 0x0000.000.00000000 0x00000001 0x00000000 1509426012
0x1a 0x80 0x06cd 0x0004 0x0000.00b6f712 0x00c00397 0x0000.000.00000000 0x00000001 0x00000000 0
0x1b 9 0x00 0x06ce 0x0002 0x0000.00b6ed4f 0x00c00397 0x0000.000.00000000 0x00000003 0x00000000 1509426012
You can see that the corresponding transaction was found
State 10 is described as the active transaction, warp# for 0X06CD and v$lock the same information, converted into 10 binary is 1741.
Oracle View transaction information by Undo block (Go)
