暂无图片
oracle 数据库中能不能查看到undo表空间中存了什么数据?
我来答
分享
秦朝阳
2022-02-11
oracle 数据库中能不能查看到undo表空间中存了什么数据?

oracle 数据库中能不能查看到undo表空间中存了什么数据?

我来答
添加附件
收藏
分享
问题补充
1条回答
默认
最新
杨卓

可以说无法直接查询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

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

暂无图片 评论
暂无图片 有用 0
打赏 0
回答交流
Markdown


请输入正文
提交
相关推荐
安装单机rac报错,安装过程没有出现问题,都是顺利安装,grid软件安装完以后,进行查看集群状态和asmcmd共享盘提示报错
回答 4
1、操作系统上实际有没有这个文件呢?/g01/app/12.2.0/grid/perl/lib/siteperl/5.10.0/x8664linuxthreadmulti/auto/DBD/Oracl
数据量大的表该怎么提升查询速度
回答 11
执行计划里面有没有回表?索引本身的碎片问题严重吗?
要迁移oracle数据库,迁移前要给客户提供迁移评估报告,有什么工具能实现?
回答 3
从Oracle到Oracle?还是?
Oracle VXBOX这个网卡为什么启用不了?
回答 8
管理>主机网络管理器先创建主机网络。
oracle 有一些表数据比较大,可以使用表压缩吗?
回答 1
已采纳
这要看该表具体用途了,如果频繁查询插入,肯定压缩特性会占用CPU时间。
数据库物化视图刷新慢问题
回答 2
已采纳
先看物化视图的模式,是基于日志的增量刷新还是全量刷新;然后分析物化视图的查询sql,看sql中是否有用到dblink,表是都在本地还是都在远端,如果都有分布那就得细看各表数据量、索引的情况了;如果只在
fork:retry:资源暂时不可用
回答 1
这个一般都是资源达到了上线,可以通过修改参数文件再切换到其他用户。每个操作系统不一样:/etc/security/limit.conf/etc/security/limits.d/90nproc.co
我设置了CLASSPATH重新安装还是一样的问题
回答 1
classpath直接指定到目录级吧?不要指定为某个jar包
Oracle:用pl/sql导入,总是死机,这是什么原因?
回答 2
已采纳
如果是用plsqldeveloper以复制粘贴的方式导入大量数据的话,界面可能会出现无响应,但这并不是死机,后台还是在运行的。另外,还要检查下是不是已经产生锁表的情况了
请问下各位大佬,服务器文件备份一般用啥工具,例如正式环境不停生成一些PDF文件,或者图片文件,为了容灾,写备份到另一个硬盘 差异备份工具有哪些?
回答 1
已采纳
可以使用一些比较常用的企业备份软件进行备份,比如:DellEMCNetWorkerVERITASNetBackupDellEMCNetWorker使用DellEMCNetWorker软件对企业应用程序