参考的这篇文章在网上流传甚广，可惜没找到作者是谁。[@more@]

1：提交当前session中的pending transaction,

SQL> show autocommit;
autocommit OFF
SQL> commit;

Commit complete.

2：确定transaction前的scn，

SQL> conn /as sysdba

SQL> select max(KTUXESCNW*power(2,32)+KTUXESCNB) from x$ktuxe;

MAX(KTUXESCNW*POWER(2,32)+KTUXESCNB)

------------------------------------

3723460

3：数据更新

SQL> conn scott/tiger

Connected.

SQL> select object_name,object_id from emp where rownum<2;

OBJECT_NAME OBJECT_ID

------------------------------ ----------

/1005bd30_LnkdConstant 17945

SQL> update emp set object_name='abc' where object_id=17945;

1 row updated.

4：查看刚刚操作的transaction及回滚段信息

SQL> select xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec

2 from v$transaction;

XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC

---------- ---------- ---------- ---------- ---------- ---------- ----------

9 14 286 2 196 44 16

SQL> select usn,name from v$rollname where usn=9;

USN NAME

---------- ------------------------------

9 _SYSSMU9$

5：Dump回滚段的头信息及块信息

SQL> alter system dump undo header '_SYSSMU9$';

System altered.

SQL>

nodepad d:oracleadminorcludumporcl_ora_1572.trc

Dump file d:oracleadminorcludumporcl_ora_1572.trc

Wed Jul 24 15:31:47 2013

ORACLE V9.2.0.8.0 - Production vsnsta=0

vsnsql=12 vsnxtr=3

Windows 2000 Version 5.2 Service Pack 2, CPU type 586

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

Windows 2000 Version 5.2 Service Pack 2, CPU type 586

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 10

Windows thread id: 1572, image: ORACLE.EXE

*** 2013-07-24 15:31:47.296

*** SESSION ID:(7.3) 2013-07-24 15:31:47.234

********************************************************************************

Undo Segment: _SYSSMU9$ (9)

********************************************************************************

Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 3 #blocks: 32

last map 0x00000000 #maps: 0 offset: 3056

Highwater:: 0x008000c4 ext#: 0 blk#: 8 ext size: 10

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

Unlocked

Map Header:: next 0x00000000 #extents: 3 obj#: 0 flag: 0x40000000

Extent Map

-----------------------------------------------------------------

0x008000bc length: 10

0x008000fd length: 11

0x008000c6 length: 11

Retention Table

-----------------------------------------------------------

Extent Number:0 Commit Time: 1364994629

Extent Number:1 Commit Time: 0

Extent Number:2 Commit Time: 1366863357

TRN CTL:: seq: 0x002c chd: 0x0003 ctl: 0x001d inc: 0x00000000 nfb: 0x0000

mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)

uba: 0x008000c4.002c.10 scn: 0x0000.003507e6

Version: 0x01

FREE BLOCK POOL::

uba: 0x00000000.002c.0f ext: 0x0 spc: 0x119c

uba: 0x00000000.0024.22 ext: 0x24 spc: 0x9ba

uba: 0x00000000.0024.11 ext: 0x24 spc: 0x1158

uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0

TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num

------------------------------------------------------------------------------------------------

0x00 9 0x00 0x011e 0x0005 0x0000.003883c5 0x008000c4 0x0000.000.00000000 0x00000001 0x00000000

。。。。。。

0x0e 10 0x80 0x011e 0x0000 0x0000.0038d0f5 0x008000c4 0x0000.000.00000000 0x00000001 0x00000000

0x0f 9 0x00 0x011e 0x0006 0x0000.00385458 0x008000be 0x0000.000.00000000 0x00000001 0x00000000

0x10 9 0x00 0x011e 0x001e 0x0000.00384d47 0x008000be 0x0000.000.00000000 0x00000001 0x00000000

。。。。。。

0x22 9 0x00 0x011d 0x0010 0x0000.00384aec 0x008000be 0x0000.000.00000000 0x00000001 0x00000000

0x22-->34 共35个事务槽。第15个事务槽state=10 表明是活动的

cflags = 0x80 -->128

wrap# = 0x011e

scn = 0x0000.0038d0f5 --> 3723509 scn is 3723460 befor update

dba = 0x008000c4 --> 00c4=196 这个是不是指的emp更新数据的数据块儿的地址？No！ file#=8 block#=196

parent-xid = 0x0000.000.00000000

在另外一篇文章中，此处的DBA被解释为回滚块的地址，如下：

dba：为回滚块的地址，根据这个地址，我们可以计算出file#及block#

　　比如： 0x0080055f　--> 055f=1375

　　 0000 0000 10 00 0000 0000 0101 0101 1111

　　 --------------------- ------------------------------------

　　 file#2　　　　　　　　　　　　　 block#1375

　　当然这个值可以不用计算，transaction的ubafil，ubablk分别为file#, block#。

0080怎么就换算成了UBAFIL file# 2 ？后四位055f可以和UBABLK对应上。

猜想： 0080 --> 0000 0000 10 00 0000 --> 10 --> 2

答： 0x0080055f --〉0x008000c4 转换成 10进制8388804，

然后使用dbms_utility.data_block_address_block

dbms_utility.data_block_address_file两个函数转换出文件号和块号

下面还有叙述。

SQL> col file_name for a40

SQL> l

1* select file_id,file_name,relative_fno from dba_data_files

SQL> /

FILE_ID FILE_NAME RELATIVE_FNO

---------- ---------------------------------------- ------------

1 D:ORACLEORADATAORCLSYSTEM01.DBF 1

2 D:ORACLEORADATAORCLUNDOTBS01.DBF 2

3 D:ORACLEORADATAORCLDRSYS01.DBF 3

4 D:ORACLEORADATAORCLINDX01.DBF 4

5 D:ORACLEORADATAORCLTOOLS01.DBF 5

6 D:ORACLEORADATAORCLUSERS01.DBF 6

7 D:ORACLEORADATAORCLXDB01.DBF 7

8 D:ORACLEORADATAORCLUSR02.DBF 8

8 rows selected.

update emp set object_name='abc' where object_id=17945;

这行数据所在的file# block#是什么呢？

参考内容：

2.1 Find the DBA for a given row

Start by getting the file and block number of the row. Example:

SQL> SELECT

2 dbms_rowid.rowid_relative_fno(rowid) REL_FNO,

3 dbms_rowid.rowid_block_number(rowid) BLOCKNO,

4 empno, ename

5 FROM emp WHERE empno = 7369;

REL_FNO BLOCKNO EMPNO ENAME

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/271063/viewspace-1060778/ ，如需转载，请注明出处，否则将追究法律责任。

ITPub博客

《Undo, Redo, DataFile看Oracle中的事务过程》学习笔记

最新文章