Use dbms_transaction to obtain the transaction ID

Use dbms_transaction to obtain the transaction ID

The most common way to find the current transaction ID and rollback segment is to execute the following SQL
SQL> select xidusn, xidslot, xidsqn from v $ transaction, v $ session where saddr = ses_addr;

XIDUSN XIDSLOT XIDSQN
------------------------------
6 27 9338

The simplest and easiest way is to use the dbms_transaction PL/SQL package. The following example shows how to use the dbms_transaction PL/SQL package to find the current transaction ID and use the undo header dump information for verification.
SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------

SQL> set transaction read write;

Transaction set.

SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
6.27.9339

6 --> undo segment number (USN) undo segment number
27 --> Transaction slot in the transaction list Transaction list
9339 --> Sequence number (the number of times the given slot has been used) serial number (number of times a given transaction slot is used)

The above transaction ID can be verified through the undo header dump information of the undo segment.
1. Find the rollback segment name
SQL> select * from v $ rollname where USN = 6;

USN NAME
----------------------------------------
6 _ SYSSMU6_3214712007 $

2. Dump rollback segment header
SQL> alter system dump undo header '_ SYSSMU6_3214712007 $ ';

System altered.

SQL> select
2 d. value | '/' | lower (rtrim (I. instance,
3 chr (0) | '_ ora _' | p. spid | '. trc' trace_file_name
4 from (select p. spid
5 from v $ mystat m,
6 v $ session s, v $ process p
7 where m. statistic # = 1 and s. sid = m. sid and p. addr = s. paddr) p,
8 (select t. instance from v $ thread t, v $ parameter v
9 where v. name = 'thread' and
10 (v. value = 0 or t. thread # = to_number (v. value) I,
11 (select value from v $ parameter
12 where name = 'user _ dump_dest ') d;

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/U01/app/Oracle/diag/rdbms/jyrac/jyrac1/trace/jyrac1_ora_16785.trc

The trace file information is as follows:
Unix process pid: 16785, image: oracle @ jyrac1 (TNS V1-V3)

* ** 09:10:02. 380
* ** Session id: (147.9205) 09:10:02. 380
* ** Client id: () 09:10:02. 380
* ** Service name :( SYS $ USERS) 09:10:02. 380
* ** Module name :( sqlplus @ jyrac1 (TNS V1-V3) 09:10:02. 380
* ** Action name :() 09:10:02. 380
 
 
**************************************** ****************************************
Undo Segment: _ SYSSMU6_3214712007 $ (6)
**************************************** ****************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header: spare1: 0 spare2: 0 # extents: 3 # blocks: 143
Last map 0x00000000 # maps: 0 offset: 4080
Highwater: 0x00c00aaa ext #: 2 blk #: 42 ext size: 128
# Blocks in seg. hdr's freelists: 0
# Blocks below: 0
Mapblk 0x00000000 offset: 2
Unlocked
Map Header: next 0x00000000 # extents: 3 obj #: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00c000d1 length: 7
0x00c00108 length: 8
0x00c00a80 length: 128

Retention Table
-----------------------------------------------------------
Extent Number: 0 Commit Time: 1461797986
Extent Number: 1 Commit Time: 1461800269
Extent Number: 2 Commit Time: 1461796031

Trn ctl: seq: 0x0c6a chd: 0x001e ctl: 0x001f inc: 0x00000000 nfb: 0x0000
Mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
Uba: 0x00c00aa6. 0c6a. 18 scn: 0x0000. 014ce06a
Version: 0x01
Free block pool ::
Uba: 0x00000000. 0c6a. 17 ext: 0x2 spc: 0x128c
Uba: 0x00000000. 0c6a. 02 ext: 0x2 spc: 0x1ace
Uba: 0x00000000. 0c6a. 14 ext: 0x2 spc: 0x13fe
Uba: 0x00000000. 0bc3. 01 ext: 0x2 spc: 0x1f84
Uba: 0x00000000. 0000.00 ext: 0x0 spc: 0x0
Trn tbl ::
 
Index state cflags wrap # uel scn dba parent-xid nub stmt_num cmt
Bytes ------------------------------------------------------------------------------------------------
0x00 9 0x00 0x2471 0x001a 0x0000. 014ce250 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804655
0x01 9 0x00 0x2479 0x0008 0x0000. 014ce7f8 0x00c00aa9 0x0000. 000.00000000 0x00000001 0x00000000 1461805256
0x02 9 0x00 0x2476 0x000c 0x0000. 014ce44e 0x00c00aa6 0x0000. 000.00000000 0x00000001 0x00000000 1461805136
0x03 9 0x00 0x2472 0x000b 0x0000. 014ce2d5 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804775
0x04 9 0x00 0x2477 0x0000 0x0000. 014ce210 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804595
0x05 9 0x00 0x2478 0x0017 0x0000. 014ce3d0 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461805015
0x06 9 0x00 0x2478 0x0018 0x0000. 014ce4db 0x00c00aa6 0x0000. 000.00000000 0x00000001 0x00000000 1461805245
0x07 9 0x00 0x2479 0x000e 0x0000. 014ce353 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804894
0x08 9 0x00 0x2476 0x0016 0x0000. 014ce815 0x00c00aa9 0x0000. 000.00000000 0x00000001 0x00000000 1461805256
0x09 9 0x00 0x247e 0x0012 0x0000. 014ce74b 0x00c00aa9 0x0000. 000.00000000 0x00000001 0x00000000 1461805247
0x0a 9 0x00 0x2477 0x001d 0x0000. 014ce10d 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804355
0x0b 9 0x00 0x247a 0x0007 0x0000. 014ce326 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804835
0x0c 9 0x00 0x2471 0x000f 0x0000. 014ce4d9 0x00c00aa9 0x0000. 000.00000000 0x00000001 0x00000000 1461805245
0x0d 9 0x00 0x2473 0x001c 0x0000. 014ce284 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804715
0x0e 9 0x00 0x2478 0x0011 0x0000. 014ce358 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804894
0x0f 9 0x00 0x2470 0x0006 0x0000. 014ce4da 0x00c00aaa 0x0000. 000.00000000 0x00000003 0x00000000 1461805245
0x10 9 0x00 0x2477 0x0001 0x0000. 014ce7e8 0x00c00aa9 0x0000. 000.00000000 0x00000001 0x00000000 1461805256
0x11 9 0x00 0x2472 0x0005 0x0000. 014ce38a 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804954
0x12 9 0x00 0x2478 0x0010 0x0000. 014ce7d8 0x00c00aa9 0x0000. 000.00000000 0x00000001 0x00000000 1461805256
0x13 9 0x00 0x2473 0x0003 0x0000. 014ce2c0 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804775
0x14 9 0x00 0x247a 0x0004 0x0000. 014ce1a2 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804474
0x15 9 0x00 0x2476 0x000a 0x0000. 014ce106 0x00000000 0x0000. 000.00000000 0x00000000 0x00000000 1461804355
0x16 9 0x00 0x246d 0x001f 0x0000. 014ce867 0x00000000 0x0000. 000.00000000 0x00000000 0x00000000 1461805315
0x17 9 0x00 0x2473 0x0019 0x0000. 014ce3f4 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461805040
0x18 9 0x00 0x247a 0x0009 0x0000. 014ce5a4 0x00c00aa9 0x0000. 000.00000000 0x00000001 0x00000000 1461805245
0x19 9 0x00 0x2475 0x0002 0x0000. 014ce441 0x00c00aa6 0x0000. 000.00000000 0x00000002 0x00000000 1461805135
0x1a 9 0x00 0x2478 0x000d 0x0000. 014ce254 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804655
0x1b 10 0x80 0x247b 0x0002 0x0000. 014ce8fc 0x00000000 0x0000. 000.00000000 0x00000000 0x00000000 0
0x1c 9 0x00 0x2477 0x0013 0x0000. 014ce2b7 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804775
0x1d 9 0x00 0x2474 0x0014 0x0000. 014ce155 0x00c00aa5 0x0000. 000.00000000 0x00000001 0x00000000 1461804415
0x1e 9 0x00 0x246d 0x0021 0x0000. 014ce079 0x00c00aa3 0x0000. 000.00000000 0x00000001 0x00000000 1461804174
0x1f 9 0x00 0x2477 0 xffff 0x0000. 014ce8de 0x00c00aa6 0x0000. 000.00000000 0x00000001 0x00000000 1461805376
0x20 9 0x00 0x2478 0x0015 0x0000. 014ce0dc 0x00000000 0x0000. 000.00000000 0x00000000 0x00000000 1461804295
0x21 9 0x00 0x2479 0x0020 0x0000. 014ce0a7 0x00c00aa3 0x0000. 000.00000000 0x00000001 0x00000000 1461804235
Ext trn ctl ::
Usn: 6
Sp1: 0x00000000 sp2: 0x00000000 sp3: 0x00000000 sp4: 0x00000000
Sp5: 0x00000000 sp6: 0x00000000 sp7: 0x00000000 sp8: 0x00000000
Ext trn tbl ::
Index extflag extHash extSpare1 extSpare2
---------------------------------------------------
0x00 0x00000000 0x00000000 0x00000000 0x00000000
0x01 0x00000000 0x00000000 0x00000000 0x00000000
0x02 0x00000000 0x00000000 0x00000000 0x00000000
0x03 0x00000000 0x00000000 0x00000000 0x00000000
0x04 0x00000000 0x00000000 0x00000000 0x00000000
0x05 0x00000000 0x00000000 0x00000000 0x00000000
0x06 0x00000000 0x00000000 0x00000000 0x00000000
0x07 0x00000000 0x00000000 0x00000000 0x00000000
0x08 0x00000000 0x00000000 0x00000000 0x00000000
0x09 0x00000000 0x00000000 0x00000000 0x00000000
0x0a 0x00000000 0x00000000 0x00000000 0x00000000
0x0b 0x00000000 0x00000000 0x00000000 0x00000000
0x0c 0x00000000 0x00000000 0x00000000 0x00000000
0x0d 0x00000000 0x00000000 0x00000000 0x00000000
0x0e 0x00000000 0x00000000 0x00000000 0x00000000
0x0f 0x00000000 0x00000000 0x00000000 0x00000000
0x10 0x00000000 0x00000000 0x00000000 0x00000000
0x11 0x00000000 0x00000000 0x00000000 0x00000000
0x12 0x00000000 0x00000000 0x00000000 0x00000000
0x13 0x00000000 0x00000000 0x00000000 0x00000000
0x14 0x00000000 0x00000000 0x00000000 0x00000000
0x15 0x00000000 0x00000000 0x00000000 0x00000000
0x16 0x00000000 0x00000000 0x00000000 0x00000000
0x17 0x00000000 0x00000000 0x00000000 0x00000000
0x18 0x00000000 0x00000000 0x00000000 0x00000000
0x19 0x00000000 0x00000000 0x00000000 0x00000000
0x1a 0x00000000 0x00000000 0x00000000 0x00000000
0x1b 0x00000000 0x00000000 0x00000000 0x00000000
0x1c 0x00000000 0x00000000 0x00000000 0x00000000
0x1d 0x00000000 0x00000000 0x00000000 0x00000000
0x1e 0x00000000 0x00000000 0x00000000 0x00000000
0x1f 0x00000000 0x00000000 0x00000000 0x00000000
0x20 0x00000000 0x00000000 0x00000000 0x00000000
0x21 0x00000000 0x00000000 0x00000000 0x00000000

From Undo Segment: _ SYSSMU6_3214712007 $ (6), we can know that the rollback Segment number is 6 and the record with the state of 10 is found.
Index state cflags wrap # uel scn dba parent-xid nub stmt_num cmt
Bytes ------------------------------------------------------------------------------------------------
0x1b 10 0x80 0x247b 0x0002 0x0000. 014ce8fc 0x00000000 0x0000. 000.00000000 0x00000000 0x00000000 0

0x1b = 27
SQL> select to_number ('1b ', 'xxx') from dual;
 
TO_NUMBER ('1b ', 'xxx ')
---------------------
27

0x247b = 9339
SQL> select to_number ('247b ', 'xxxx') from dual;
 
TO_NUMBER ('247b ', 'xxxx ')
------------------------
9339

From the above information, we can see that it is consistent with the transaction ID we obtained using the dbms_transaction PL/SQL package.

This article permanently updates the link address: