可以说无法直接查询undo里面存储的数据,undo blocks存储的数据dump出来我们看也很难看懂。 

1.已提交的记录可以通过oracle redo logminer挖掘得到sql_undo的执行的历史sql记录！

2.从原理上将undo就是存储修改数据的前镜像，专用于回滚使用

https://blog.csdn.net/tianlesoftware/article/details/6672417
--操作前,无事务
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
no rows selected
SQL> select s.usn,n.name,s.extents,s.hwmsize,s.status from v$rollstat s, v$rollname n where s.usn=n.usn;
USN NAME EXTENTS HWMSIZE STATUS
---------- ------------------------------ ---------- ---------- ---------------
0 SYSTEM 6 385024 ONLINE
1 _SYSSMU1_3724004606$ 4 2220032 ONLINE
2 _SYSSMU2_2996391332$ 4 2220032 ONLINE
3 _SYSSMU3_1723003836$ 15 12771328 ONLINE
4 _SYSSMU4_1254879796$ 4 253952 ONLINE
5 _SYSSMU5_898567397$ 4 2220032 ONLINE
6 _SYSSMU6_1263032392$ 3 1171456 ONLINE
7 _SYSSMU7_2070203016$ 11 9560064 ONLINE
8 _SYSSMU8_517538920$ 3 1171456 ONLINE
9 _SYSSMU9_1650507775$ 4 2220032 ONLINE
10 _SYSSMU10_1197734989$ 4 2220032 ONLINE
11 rows selected.

SQL> select file_name,file_id,TABLESPACE_NAME from dba_data_FILES WHERE TABLESPACE_NAME='UNDOTBS1';
FILE_NAME FILE_ID TABLESPACE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/gbkt1/gbkt1/undotbs01.dbf 3 UNDOTBS1

--制造一个事务
[oracle@t1 ~]$ sqlplus yz/yz
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 16 07:29:27 2022
SQL> select rowid,object_id from a where rownum=1;
ROWID OBJECT_ID
------------------ ----------
AAAV/KAAFAAAFUDAAA 20
SQL> delete a where rowid='AAAV/KAAFAAAFUDAAA';
1 row deleted.

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
3 0 1462 6569 3 19
-- 3 _SYSSMU3_1723003836$ 15 12771328 ONLINE


SQL>alter system dump undo header '_SYSSMU3_1723003836$';
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/gbkt1/gbkt1/trace/gbkt1_ora_54154.trc

*** 2022-02-16 07:34:04.151
********************************************************************************
Undo Segment: _SYSSMU3_1723003836$ (3)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 15 #blocks: 1559
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00c019a9 ext#: 11 blk#: 41 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 11
Unlocked
Map Header:: next 0x00000000 #extents: 15 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00c000a1 length: 7
0x00c00178 length: 8
0x00c01480 length: 128
0x00c01500 length: 128
0x00c01580 length: 128
0x00c01600 length: 128
0x00c01680 length: 128
0x00c01700 length: 128
0x00c01800 length: 128
0x00c01880 length: 128
0x00c01900 length: 128
0x00c01980 length: 128
0x00c00170 length: 8
0x00c01400 length: 128
0x00c00180 length: 128

Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1645007688
Extent Number:1 Commit Time: 1645007688
Extent Number:2 Commit Time: 1645007688
Extent Number:3 Commit Time: 1645007688
Extent Number:4 Commit Time: 1645007688
Extent Number:5 Commit Time: 1645007688
Extent Number:6 Commit Time: 1645007688
Extent Number:7 Commit Time: 1645007688
Extent Number:8 Commit Time: 1645007688
Extent Number:9 Commit Time: 1645007688
Extent Number:10 Commit Time: 1645007693
Extent Number:11 Commit Time: 1645007693
Extent Number:12 Commit Time: 1644944701
Extent Number:13 Commit Time: 1645007688
Extent Number:14 Commit Time: 1645007688

TRN CTL:: seq: 0x01a3 chd: 0x0010 ctl: 0x0020 inc: 0x00000000 nfb: 0x0000
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c019a9.01a3.13 scn: 0x0000.0044aad0
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.01a3.12 ext: 0xb spc: 0x1582
uba: 0x00000000.01a3.15 ext: 0xb spc: 0x260
uba: 0x00000000.0196.27 ext: 0x2 spc: 0x230
uba: 0x00000000.0112.22 ext: 0xf spc: 0x7ae
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 10 0x80 0x05b6 0x000b 0x0000.0044b44a 0x00c019a9 0x0000.000.00000000 0x00000001 0x00000000 0
0x01 9 0x00 0x05b5 0x0009 0x0000.0044ad59 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645021303
0x02 9 0x00 0x05b4 0x001c 0x0000.0044ab5f 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x03 9 0x00 0x05b5 0x0019 0x0000.0044ab43 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x04 9 0x00 0x05b6 0x000c 0x0000.0044af1c 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645022502
0x05 9 0x00 0x05b4 0x0021 0x0000.0044ad2f 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645021302
0x06 9 0x00 0x05b5 0x0013 0x0000.0044ab03 0x00c019a7 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x07 9 0x00 0x05b4 0x0015 0x0000.0044ab7b 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x08 9 0x00 0x05b5 0x0001 0x0000.0044ad4a 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645021302
0x09 9 0x00 0x05b5 0x0004 0x0000.0044af0e 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645022502
0x0a 9 0x00 0x05b4 0x001f 0x0000.0044ab93 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x0b 9 0x00 0x05b5 0x0011 0x0000.0044acbc 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645021124
0x0c 9 0x00 0x05b4 0x001e 0x0000.0044b060 0x00c019a9 0x0000.000.00000000 0x00000001 0x00000000 1645023403
0x0d 9 0x00 0x05b2 0x0012 0x0000.0044ad14 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645021302
0x0e 9 0x00 0x05b5 0x001b 0x0000.0044ab1f 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x0f 9 0x00 0x05b5 0x0014 0x0000.0044b383 0x00c019a9 0x0000.000.00000000 0x00000001 0x00000000 1645025366
0x10 9 0x00 0x05b3 0x0017 0x0000.0044aadd 0x00c019a7 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x11 9 0x00 0x05b4 0x000d 0x0000.0044ad05 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645021302
0x12 9 0x00 0x05b4 0x0005 0x0000.0044ad24 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645021302
0x13 9 0x00 0x05b5 0x000e 0x0000.0044ab10 0x00c019a7 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x14 9 0x00 0x05b3 0x0020 0x0000.0044b3da 0x00c019a9 0x0000.000.00000000 0x00000001 0x00000000 1645025467
0x15 9 0x00 0x05b4 0x000a 0x0000.0044ab88 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x16 9 0x00 0x05b3 0x000f 0x0000.0044b359 0x00c019a9 0x0000.000.00000000 0x00000001 0x00000000 1645025327
0x17 9 0x00 0x05b3 0x001d 0x0000.0044aae8 0x00c019a7 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x18 9 0x00 0x05b3 0x0016 0x0000.0044b32e 0x00c019a9 0x0000.000.00000000 0x00000001 0x00000000 1645025327
0x19 9 0x00 0x05b4 0x0002 0x0000.0044ab50 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x1a 9 0x00 0x05b4 0x0003 0x0000.0044ab38 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x1b 9 0x00 0x05b4 0x001a 0x0000.0044ab2b 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x1c 9 0x00 0x05b3 0x0007 0x0000.0044ab6e 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x1d 9 0x00 0x05b4 0x0006 0x0000.0044aaf5 0x00c019a7 0x0000.000.00000000 0x00000001 0x00000000 1645020391
0x1e 9 0x00 0x05af 0x0018 0x0000.0044b06e 0x00c019a9 0x0000.000.00000000 0x00000001 0x00000000 1645023403
0x1f 9 0x00 0x05af 0x000b 0x0000.0044ac42 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645020835
0x20 9 0x00 0x05b5 0xffff 0x0000.0044b418 0x00c019a9 0x0000.000.00000000 0x00000001 0x00000000 1645025470
0x21 9 0x00 0x05b5 0x0008 0x0000.0044ad3d 0x00c019a8 0x0000.000.00000000 0x00000001 0x00000000 1645021302
EXT TRN CTL::
usn: 3
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
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
3 0 1462------6569---------3 19
alter system dump datafile 3 block 6569;

······
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0004.008.00000524 uba: 0x00c000db.0283.14
flg: C--- lkc: 0 scn: 0x0000.0044af10
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00400c9a hdba: 0x00400c98
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 187(0xbb) flag: 0x2c lock: 0 ckix: 9
ncol: 9 nnew: 7 size: 0
col 2: [ 3] c2 02 4c
col 3: [ 2] c1 0d
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 7a 02 10 07 07 21

*-----------------------------
* Rec #0x2 slt: 0x0c objn: 480(0x000001e0) objd: 480 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x01
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c019a9.01a3.01
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00400c9a hdba: 0x00400c98
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 188(0xbc) flag: 0x2c lock: 0 ckix: 9
ncol: 9 nnew: 7 size: 0
col 2: [ 3] c2 02 4a
col 3: [ 2] c1 0c
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 7a 02 10 07 07 21

*-----------------------------