Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to view transaction information in Oracle". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Database version:

Oracle 11.2.0.3 RAC

Experimental purpose:

View Oracle transaction information through undo blocks

Details of the experiment:

1 start 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.

This transaction does not require commit or rollback.

2 View transaction information

A) check the session number:

SQL > select sid from v$mystat where rownum=1

SID

-

twenty-seven

B) obtain information such as undo slot:

SQL > select xidusn,xidslot,xidsqn from V$TRANSACTION tjorie vandalism 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

Among them

Column Description

XIDUSN Undo segment number

XIDSLOT Slot number

XIDSQN Sequence number

3 find the undo block according to the queried information:

A) View the location of undo blocks

SQL > clear columns

Columns cleared

SQL > col name for A40

SQL > select * from v$rollname where usn=10

USN NAME

10 _ 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 undo block 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 10 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 has been found.

If state is 10, it means active transaction, and if warp# is 0x06cd, the information found by v$lock is the same as that found by v$lock, which translates to 1741 in decimal system.

This is the end of the content of "how to view transaction information in Oracle". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.