---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
----------------------------------------------------------
Plan hash value: 2489195056
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 50 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| SALGRADE | 5 | 50 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
165 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
621 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
5 rows processed
---------- ---------- ----------- --------- ---------- ----------
4 41 227 1885 2 16
---------- ---------- ---------- ---------- ---------- ---------- ----------
0 7620 385024 0 385024 0 0
1 864622 188416 0 516096 1 15
2 1680594 319488 0 909312 2 32
3 1430048 2088960 0 2088960 0 16
4 1397364 2088960 1 2088960 0 16
5 1775740 2088960 0 2088960 0 16
6 1238998 2088960 0 2088960 0 16
7 1124482 253952 0 909312 1 20
8 6811862 1236992 0 6283264 2 33
9 1569972 253952 0 2088960 4 29
10 1098782 188416 0 2088960 3 20
---------- ------------------------------
4 _SYSSMU4$
from
(select value from v$parameter where name='user_dump_dest') a,
(select substr(value,-6,1) symbol from v$parameter where name='user_dump_dest') b,
(select instance_name from v$instance) c,
(select spid from v$session s,v$process p,v$mystat m where s.paddr=p.addr
and s.sid=m.sid and m.statistic#=0) d
/
/home/oracle/admin/oratest/udump/oratest_ora_7108.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.9-42.0.0.0.1.ELhugemem
Version: #1 SMP Sun Oct 15 14:06:18 PDT 2006
Machine: i686
Instance name: oratest
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 7108, p_w_picpath: oracle@localhost.localdomain (TNS V1-V3)
********************************************************************************
Undo Segment: _SYSSMU4$ (4)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 17 #blocks: 255
localhost->
localhost-> more /home/oracle/admin/oratest/udump/oratest_ora_7108.trc
/home/oracle/admin/oratest/udump/oratest_ora_7108.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub
stmt_num cmt
-----------------------------------------------------------------------------------------
-------
0x29 10 0x80 0x00e3 0x000e 0x0000.000839fb 0x0080075d 0x0000.000.00000000 0x0
0000001 0x00000000 0
0x2a 9 0x00 0x00e2 0x0026 0x0000.00083999 0x00800746 0x0000.000.00000000 0x0
0000001 0x00000000 1301748946
0x2b 9 0x00 0x00e2 0x002a 0x0000.00083987 0x00800745 0x0000.000.00000000 0x0
0000001 0x00000000 1301748946
0x2c 9 0x00 0x00e2 0x001f 0x0000.00083a2b 0x0080074b 0x0000.000.00000000 0x0
0000003 0x00000000 1301749223
0x2d 9 0x00 0x00e2 0x002c 0x0000.00083a2a 0x00800749 0x0000.000.00000000 0x0
0000003 0x00000000 1301749223
0x2e 9 0x00 0x00e2 0x0000 0x0000.00083a3a 0x0080075e 0x0000.000.00000000 0x0
0000007 0x00000000 1301749234
0x2f 9 0x00 0x00e2 0x0002 0x0000.00083a2f 0x00800751 0x0000.000.00000000 0x0
0000003 0x00000000 1301749225
我们可以看到事物状态为10,说明是活动的事务,事务槽位0x29
---------------------------------
/home/oracle/admin/oratest/udump/oratest_ora_7307.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.9-42.0.0.0.1.ELhugemem
Version: #1 SMP Sun Oct 15 14:06:18 PDT 2006
Machine: i686
Instance name: oratest
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 7307, p_w_picpath: oracle@localhost.localdomain (TNS V1-V3)
*** ACTION NAME:() 2011-04-02 21:36:09.979
*** MODULE NAME:(SQL*Plus) 2011-04-02 21:36:09.978
*** SERVICE NAME:(SYS$USERS) 2011-04-02 21:36:09.978
*** SESSION ID:(140.89) 2011-04-02 21:36:09.977
Start dump data blocks tsn: 1 file#: 2 minblk 1885 maxblk 1885
buffer tsn: 1 rdba: 0x0080075d (2/1885)
scn: 0x0000.00084049 seq: 0x01 flg: 0x00 tail: 0x40490201
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC0F600 to 0x0CC11600
CC0F600 0000A202 0080075D 00084049 00010000 [....]...I@......]
xid: 0x0004.029.000000e3 seq: 0xa9 cnt: 0x12 irb: 0x12 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f70 0x02 0x1f1c 0x03 0x1e70 0x04 0x1df4 0x05 0x1d88
0x06 0x1d1c 0x07 0x1c70 0x08 0x1c04 0x09 0x1b98 0x0a 0x1aec
0x0b 0x1a44 0x0c 0x199c 0x0d 0x18f0 0x0e 0x1844 0x0f 0x17d8
0x10 0x173c 0x11 0x16e0 0x12 0x1684
.................
* Rec #0x9 slt: 0x23 objn: 517(0x00000205) objd: 517 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x08
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
op: C uba: 0x0080075d.00a9.08
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0040100d hdba: 0x00401001
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 45(0x2d) flag: 0x2c lock: 0 ckix: 254
ncol: 9 nnew: 7 size: 0
Vector content:
col 2: [ 1] 80
col 3: [ 2] c1 08
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 6f 04 02 15 15 25
*-----------------------------
* Rec #0xa slt: 0x1e objn: 517(0x00000205) objd: 517 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x0080075d.00a9.07 ctl max scn: 0x0000.000837ed prv tx scn: 0x0000.00083814
txn start scn: scn: 0x0000.00083d3e logon user: 0
prev brb: 8390469 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0008.018.00000116 uba: 0x008000ee.00eb.31
flg: C--- lkc: 0 scn: 0x0000.00083d37
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0040100d hdba: 0x00401001
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 62(0x3e) flag: 0x2c lock: 0 ckix: 254
ncol: 9 nnew: 7 size: 0
Vector content:
col 2: [ 1] 80
col 3: [ 2] c1 08
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 6f 04 02 15 15 25
............................
*-----------------------------
* Rec #0x10 slt: 0x29 objn: 51151(0x0000c7cf) objd: 51151 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x0080075d.00a9.0e ctl max scn: 0x0000.00083918 prv tx scn: 0x0000.0008393e
txn start scn: scn: 0x0000.00083b17 logon user: 54
prev brb: 8390469 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 10
ncol: 3 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000038 hdba: 0x01000033
itli: 2 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 2] c2 08
*-----------------------------
* Rec #0x11 slt: 0x29 objn: 51151(0x0000c7cf) objd: 51151 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x10
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
op: C uba: 0x0080075d.00a9.10
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000038 hdba: 0x01000033
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 10
ncol: 3 nnew: 1 size: 1
col 1: [ 3] c2 0d 02
*-----------------------------
* Rec #0x12 slt: 0x29 objn: 51151(0x0000c7cf) objd: 51151 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x11
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
op: C uba: 0x0080075d.00a9.11
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01000038 hdba: 0x01000033
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 10
ncol: 3 nnew: 1 size: 1
col 1: [ 3] c2 0f 02
End dump data blocks tsn: 1 file#: 2 minblk 1885 maxblk 1885
xid: 0x0004.029.000000e3 seq: 0xa9 cnt: 0x12 irb: 0x12 icl: 0x0 flg: 0x0000
irb指的是回滚段中记录的最近未提交变更开始之处,如果开始回滚,这是起始的搜索点。
---------------------------------------------------------------------------
0x01 0x1f70 0x02 0x1f1c 0x03 0x1e70 0x04 0x1df4 0x05 0x1d88
0x06 0x1d1c 0x07 0x1c70 0x08 0x1c04 0x09 0x1b98 0x0a 0x1aec
0x0b 0x1a44 0x0c 0x199c 0x0d 0x18f0 0x0e 0x1844 0x0f 0x17d8
0x10 0x173c 0x11 0x16e0 0x12 0x1684
* Layer: 11 (Row) opc: 1 rci 0x11
ncol: 3 nnew: 1 size: 1
col 1: [ 3] c2 0f 02
我们来看看c2 0f 02这个值代表是什么意思:
-------------------
Typ=2 Len=3: c2,f,2
-----------------
Typ=2 Len=2: c2,8
-------------------
Typ=2 Len=3: c2,d,2
from x$bh a,dba_extents b where b.relative_fno=a.dbarfil
and b.block_id <= a.dbablk and b.block_id + b.blocks > a.dbablk
and b.owner='SCOTT'
and b.segment_name='SALGRADE';
------------------------------ ---------- ---------- ---------- ---------- ----------
SALGRADE 4 4 52 1 1
SALGRADE 4 4 54 1 1
SALGRADE 4 4 51 4 1
SALGRADE 4 4 56 1 1
SALGRADE 4 4 56 1 3
SALGRADE 4 4 56 1 3
SALGRADE 4 4 56 1 3
SALGRADE 4 4 53 1 1
SALGRADE 4 4 55 1 1
SQL> alter system dump datafile 4 block 56;
--------------------------------------------------------------------------------
/home/oracle/admin/oratest/udump/oratest_ora_7762.trc
/home/oracle/admin/oratest/udump/oratest_ora_7762.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.9-42.0.0.0.1.ELhugemem
Version: #1 SMP Sun Oct 15 14:06:18 PDT 2006
Machine: i686
Instance name: oratest
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 7762, p_w_picpath: oracle@localhost.localdomain (TNS V1-V3)
*** SERVICE NAME:(SYS$USERS) 2011-04-02 23:11:43.080
*** SESSION ID:(145.211) 2011-04-02 23:11:43.079
Start dump data blocks tsn: 4 file#: 4 minblk 56 maxblk 56
buffer tsn: 4 rdba: 0x01000038 (4/56)
scn: 0x0000.00084049 seq: 0x01 flg: 0x04 tail: 0x40490601
frmt: 0x02 chkval: 0x8774 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CC0F600 to 0x0CC11600
CC0F600 0000A206 01000038 00084049 04010000 [....8...I@......]
CC0F610 00008774 00020001 0000C7CF 00083DC9 [t............=..]
CC0F620 1FE80000 00321F02 01000031 001C0009 [......2.1.......]
CC0F630 00000112 00804D5A 002B00BC 00008000 [....ZM....+.....]
CC0F640 0006BFFA 00290004 000000E3 0080075D [......).....]...]
CC0F650 001200A9 00020003 00000000 00000000 [................]
CC0F660 00000000 00050100 001CFFFF 1F3B1F3F [............?.;.]
CC0F670 00001F3D 1F8C0005 1F3F1F4B 1F571F65 [=.......K.?.e.W.]
CC0F680 00000000 00000000 00000000 00000000 [................]
Repeat 497 times
CC115A0 2C000000 C1020302 0DC20204 2C15C202 [...,...........,]
CC115B0 C1020302 0CC20203 2C0FC202 C1020300 [...........,....]
CC115C0 1FC20306 64C20302 03002C64 0305C102 [.......dd,......]
CC115D0 020215C2 002C1FC2 04C10203 020FC203 [......,.........]
CC115E0 2C15C202 C1020300 0DC20303 0FC20202 [...,............]
CC115F0 0203022C C20202C1 0DC2020B 40490601 [,.............I@]
Block header dump: 0x01000038
Object id on Block? Y
seg/obj: 0xc7cf csc: 0x00.83dc9 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000031 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.01c.00000112 0x00804d5a.00bc.2b C--- 0 scn 0x0000.0006bffa
0x02 0x0004.029.000000e3 0x0080075d.00a9.12 ---- 3 fsc 0x0002.00000000
data_block_dump,data header at 0xcc0f664
===============
tsiz: 0x1f98
hsiz: 0x1c
pbl: 0x0cc0f664
bdba: 0x01000038
76543210
flag=--------
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f3f
avsp=0x1f3b
tosp=0x1f3d
0xe:pti[0] nrow=5 offs=0
0x12:pri[0] offs=0x1f8c
0x14:pri[1] offs=0x1f4b
0x16:pri[2] offs=0x1f3f
0x18:pri[3] offs=0x1f65
0x1a:pri[4] offs=0x1f57
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 02
col 1: [ 2] c2 0b
col 2: [ 2] c2 0d
tab 0, row 1, @0x1f4b
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 03
col 1: [ 2] c2 0c
col 2: [ 2] c2 0f
tab 0, row 2, @0x1f3f
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 04
col 1: [ 2] c2 0d
col 2: [ 2] c2 15
tab 0, row 3, @0x1f65
tl: 13 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 05
col 1: [ 3] c2 15 02
col 2: [ 2] c2 1f
tab 0, row 4, @0x1f57
tl: 14 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 06
col 1: [ 3] c2 1f 02
col 2: [ 3] c2 64 64
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 56 maxblk 56
stmt_num cmt
-----------------------------------------------------------------------------------------
-------
0x29 10 0x80 0x00e3 0x000e 0x0000.000839fb 0x0080075d 0x0000.000.00000000 0x0
0000001 0x00000000 0
同时我们还可以看到,该块上存在指向会滚段的事务信息,这个事务地址就是UBA(UNDO BLOCK ADDRESS)0x0080075d.00a9.12,我们来分析该UBA代表的意思
xid: 0x0004.029.000000e3 seq: 0xa9 cnt: 0x12 irb: 0x12 icl: 0x0 flg: 0x0000
我们继续分析改块的信息,锁定位置lb指向0x02号事务槽,
col 0: [ 2] c1 03
col 1: [ 2] c2 0c
col 2: [ 2] c2 0f
tab 0, row 2, @0x1f3f
tl: 12 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 04
col 1: [ 2] c2 0d
col 2: [ 2] c2 15
tab 0, row 3, @0x1f65
tl: 13 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 05
col 1: [ 3] c2 15 02
col 2: [ 2] c2 1f
tab 0, row 4, @0x1f57
tl: 14 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 06
col 1: [ 3] c2 1f 02
col 2: [ 3] c2 64 64
⑵ 在数据块头部获取一个ITL事务槽,该事务槽指向回滚段头的事务槽。
⑶ 在修改数据之前,需要记录前镜像信息,这个信息以UNDO RECORD的形式存储在回滚段中,回滚段头事务槽指向该记录。
⑷ 锁定修改行,修改行锁定位指向ITL事务槽。
⑸ 数据修改可以进行。