參考的這篇文章在網上流傳甚廣，可惜沒找到作者是誰。[@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--&gt34 共35個事務槽。第15個事務槽state=10 表明是活動的

cflags = 0x80 --&gt128

wrap# = 0x011e

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

dba = 0x008000c4 --&gt 00c4=196 這個是不是指的emp更新資料的資料塊兒的地址？No！ file#=8 block#=196

parent-xid = 0x0000.000.00000000

在另外一篇文章中，此處的DBA被解釋為回滾塊的地址，如下：

dba：為回滾塊的地址，根據這個地址，我們可以計算出file#及block#

　　比如： 0x0080055f　--&gt 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 --&gt 0000 0000 10 00 0000 --&gt 10 --&gt 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部落格 ” ，連結：http://blog.itpub.net/271063/viewspace-1060778/，如需轉載，請註明出處，否則將追究法律責任。

《Undo, Redo, DataFile看Oracle中的事務過程》學習筆記

相關文章