v$transaction中有哪些回滚段信息-创新互联
本篇内容主要讲解“v$transaction中有哪些回滚段信息”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“v$transaction中有哪些回滚段信息”吧!
SQL> select * from v$rollname a where a.usn=6; USN NAME ---------- ------------------------------ 6 _SYSSMU6_3707530756$ SQL> select usn,writes,rssize,xacts,hwmsize,shrinks,wraps from v$rollstat; USN WRITES RSSIZE XACTS HWMSIZE SHRINKS WRAPS ---------- ---------- ---------- ---------- ---------- ---------- ---------- 0 8112 385024 0 385024 0 0 1 800750 2220032 0 2220032 0 3 2 1412340 2220032 0 2220032 0 1 3 1398866 2220032 0 2220032 0 3 4 1569748 2220032 0 2220032 0 4 5 2109384 3268608 0 3268608 0 4 6 4877548 5365760 1 5365760 0 9 7 1672696 2220032 0 2220032 0 2 8 1341938 2220032 0 2220032 0 1 9 1542752 2220032 0 2220032 0 5 10 1879432 4317184 0 4317184 0 2
alter system dump undo header '_SYSSMU6$'; 转储undo块头
转储内容：
[oracle@parnassusdata1 trace]$ tail -n 300 PROD1_ora_10132.trc Trace file /s01/oracle/app/oracle/diag/rdbms/prod/PROD1/trace/PROD1_ora_10132.trc Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /s01/oracle/app/oracle/product/11.2.0/dbhome_1 System name: Linux Node name: parnassusdata1.oracle.com Release: 2.6.32-200.13.1.el5uek Version: #1 SMP Wed Jul 27 21:02:33 EDT 2011 Machine: x86_64 Instance name: PROD1 Redo thread mounted by this instance: 1 Oracle process number: 50 Unix process pid: 10132, image: oracle@parnassusdata1.oracle.com (TNS V1-V3) *** 2018-09-17 02:18:24.629 *** SESSION ID:(77.73) 2018-09-17 02:18:24.629 *** CLIENT ID:() 2018-09-17 02:18:24.629 *** SERVICE NAME:(SYS$USERS) 2018-09-17 02:18:24.629 *** MODULE NAME:(SQL*Plus) 2018-09-17 02:18:24.629 *** ACTION NAME:() 2018-09-17 02:18:24.629 ******************************************************************************** Undo Segment: _SYSSMU6_3707530756$ (6) ******************************************************************************** Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 7 #blocks: 655 last map 0x00000000 #maps: 0 offset: 4080 Highwater:: 0x00c00a47 ext#: 4 blk#: 71 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 4 Unlocked Map Header:: next 0x00000000 #extents: 7 obj#: 0 flag: 0x40000000 Extent Map ----------------------------------------------------------------- 0x00c000d1 length: 7 0x00c000d8 length: 8 0x00c00800 length: 128 0x00c00980 length: 128 0x00c00a00 length: 128 0x00c00280 length: 128 0x00c00700 length: 128 Retention Table ----------------------------------------------------------- Extent Number:0 Commit Time: 1537121357 Extent Number:1 Commit Time: 1537121357 Extent Number:2 Commit Time: 1537121357 Extent Number:3 Commit Time: 1537121357 Extent Number:4 Commit Time: 1537121357 Extent Number:5 Commit Time: 1537121357 Extent Number:6 Commit Time: 1537121357 TRN CTL:: seq: 0x0232 chd: 0x001d ctl: 0x000a inc: 0x00000000 nfb: 0x0000 mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x00c00a47.0232.2d scn: 0x0000.006d9937 Version: 0x01 FREE BLOCK POOL:: uba: 0x00000000.0232.2c ext: 0x4 spc: 0x836 uba: 0x00000000.022c.28 ext: 0x2 spc: 0xad6 uba: 0x00000000.022c.09 ext: 0x2 spc: 0x4d0 uba: 0x00000000.0218.01 ext: 0x2 spc: 0x1f84 uba: 0x00000000.0092.01 ext: 0x18 spc: 0x1ed0 TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0x0972 0x0002 0x0000.006d999d 0x00c00a46 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x01 9 0x00 0x096f 0x0005 0x0000.006d9a63 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x02 9 0x00 0x0971 0x001c 0x0000.006d99aa 0x00c00a46 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x03 9 0x00 0x0970 0x001f 0x0000.006d9af1 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x04 9 0x00 0x0975 0x000d 0x0000.006d9aa3 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x05 9 0x00 0x0972 0x0006 0x0000.006d9a72 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x06 9 0x00 0x0970 0x0007 0x0000.006d9a81 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x07 9 0x00 0x0968 0x0004 0x0000.006d9a92 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x08 9 0x00 0x096f 0x001a 0x0000.006d99e8 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x09 9 0x00 0x0972 0x0000 0x0000.006d9990 0x00c00a46 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x0a 9 0x00 0x0973 0xffff 0x0000.006d9b44 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121394 0x0b 9 0x00 0x0972 0x000f 0x0000.006d9a41 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x0c 9 0x00 0x0974 0x0009 0x0000.006d9982 0x00c00a46 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x0d 9 0x00 0x0974 0x0019 0x0000.006d9ab3 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x0e 9 0x00 0x0970 0x0008 0x0000.006d99da 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x0f 9 0x00 0x08bc 0x0001 0x0000.006d9a52 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x10 9 0x00 0x0971 0x0014 0x0000.006d9a23 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x11 9 0x00 0x0974 0x000a 0x0000.006d9b39 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121394 0x12 9 0x00 0x096e 0x0013 0x0000.006d9a06 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x13 9 0x00 0x096f 0x0010 0x0000.006d9a15 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x14 9 0x00 0x0973 0x000b 0x0000.006d9a33 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x15 9 0x00 0x0971 0x000c 0x0000.006d9972 0x00c00a46 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x16 10 0x80 0x096e 0x0004 0x0000.006d9b4f 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 0 0x17 9 0x00 0x096e 0x0003 0x0000.006d9ae3 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x18 9 0x00 0x0970 0x0011 0x0000.006d9b2d 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121394 0x19 9 0x00 0x096f 0x0021 0x0000.006d9ac3 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x1a 9 0x00 0x0971 0x0012 0x0000.006d99f8 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x1b 9 0x00 0x0970 0x0020 0x0000.006d9952 0x00c00a46 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x1c 9 0x00 0x0971 0x001e 0x0000.006d99bb 0x00c00a46 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x1d 9 0x00 0x0972 0x001b 0x0000.006d9945 0x00c00a46 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x1e 9 0x00 0x0972 0x000e 0x0000.006d99cc 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x1f 9 0x00 0x0973 0x0018 0x0000.006d9b11 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121361 0x20 9 0x00 0x0970 0x0015 0x0000.006d9962 0x00c00a46 0x0000.000.00000000 0x00000001 0x00000000 1537121359 0x21 9 0x00 0x096f 0x0017 0x0000.006d9ad4 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 1537121359 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
分析关键信息：
0x16 10 0x80 0x096e 0x0004 0x0000.006d9b4f 0x00c00a47 0x0000.000.00000000 0x00000001 0x00000000 0
状态（State）为 10 代表的是活动事务，该事务正好占用的是第 22 号事务槽(0x16)
[oracle@parnassusdata1 trace]$ printf %d 0x17 23[oracle@parnassusdata1 trace]$ printf %d 0x16 22[oracle@parnassusdata1 trace]$
DBA（Data Block Address），这个 DBA 指向的就是包含这个事务的前镜像的数据块地址:
0x00c00a47
DBA 代表数据块的存储地址，由 10 位文件号 + 22 位数据块（Block）组成。
先把16变10
8388693[oracle@parnassusdata1 trace]$ printf %d 0x00c00a47
12585543
然后将
前面用0补齐32位
00000000110000000000101001000111
经过转换后，该前镜像信息位于 file3 block 2631。
这和我们从事务表中查询得到的数据完全一致：
SQL> SELECT xidusn, xidslot, xidsqn, ubablk, ubafil, ubarec FROM v$transaction; XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC ---------- ---------- ---------- ---------- ---------- ---------- 6 22 2414 2631 3 45
说明前进行位于 3号文件，2631数据块
get新技能：
[oracle@parnassusdata1 trace]$ printf %d 0x17 23[oracle@parnassusdata1 trace]$ printf %d 0x16 22[oracle@parnassusdata1 trace]$
到此，相信大家对“v$transaction中有哪些回滚段信息”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
分享文章：v$transaction中有哪些回滚段信息-创新互联
当前地址：http://azwzsj.com/article/epdgi.html
