author :direction
qq :249601591
以下内容为个人测试加猜测,仅供参考。对此引发的任何后果不承担任何责任,谢谢!
如有错误还望指正,如有新的见解欢迎指导。
qq :249601591
以下内容为个人测试加猜测,仅供参考。对此引发的任何后果不承担任何责任,谢谢!
如有错误还望指正,如有新的见解欢迎指导。
Release 10.2.0.1.0
13:22:50 sys@prod(prod)> create tablespace test datafile '/u01/app/oracle/oradata/prod/test01.dbf' size 50M;
Tablespace created.
13:24:10
sys@prod(prod)> create table test(a varchar(20)) tablespace test;
Table created.
Table created.
13:27:47
sys@prod(prod)> commit;
Commit complete.
Commit complete.
select rowid,
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,
dbms_rowid.rowid_row_number(rowid) num ,
rowidtochar(rowid) from test where rownum=1
/
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID)
------------------ ---------- ---------- ---------- ---------- ------------------
AAANXIAAIAAAAANAAA 54728 8 13 0 AAANXIAAIAAAAANAAA
------------------ ---------- ---------- ---------- ---------- ------------------
AAANXIAAIAAAAANAAA 54728 8 13 0 AAANXIAAIAAAAANAAA
13:31:22
sys@prod(prod)> oradebug setmypid
Statement processed.
14:55:47 sys@prod(prod)> oradebug tracefile_name
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
14:55:54 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
Statement processed.
14:55:47 sys@prod(prod)> oradebug tracefile_name
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
14:55:54 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/dbhome_1
System name: Linux
Node name: prod
Release: 2.6.18-238.el5
Version: #1 SMP Sun Dec 19 14:22:44 EST 2010
Machine: x86_64
Instance name: prod
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 26336, image: oracle@prod (TNS V1-V3)
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/dbhome_1
System name: Linux
Node name: prod
Release: 2.6.18-238.el5
Version: #1 SMP Sun Dec 19 14:22:44 EST 2010
Machine: x86_64
Instance name: prod
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 26336, image: oracle@prod (TNS V1-V3)
*** 2012-12-13 14:55:39.774
*** SERVICE NAME:(SYS$USERS) 2012-12-13 14:55:39.774
*** SESSION ID:(132.18) 2012-12-13 14:55:39.774
Start dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
buffer tsn: 8 rdba: 0x0200000d (8/13)
scn: 0x0000.0028eb7b seq: 0x03 flg: 0x02 tail: 0xeb7b0603
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000D 0028EB7B 02030000 [........{.(.....]
011C6D410 00000000 00000001 0000D5C8 0028EB6F [............o.(.]
011C6D420 00000000 00320002 02000009 00240006 [......2.......$.]
011C6D430 0000065B 00800209 0016027F 00002001 [[............ ..]
011C6D440 0028EB7B 00000000 00000000 00000000 [{.(.............]
011C6D450 00000000 00000000 00000000 00000000 [................]
011C6D460 00000000 00010100 0014FFFF 1F7A1F8E [..............z.]
011C6D470 00001F7A 1F8E0001 00000000 00000000 [z...............]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
011C6F3F0 012C0000 726F0601 65676E61 EB7B0603 [..,...orange..{.] <= 保存的记录
Block header dump: 0x0200000d
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28eb6f itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
*** SERVICE NAME:(SYS$USERS) 2012-12-13 14:55:39.774
*** SESSION ID:(132.18) 2012-12-13 14:55:39.774
Start dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
buffer tsn: 8 rdba: 0x0200000d (8/13)
scn: 0x0000.0028eb7b seq: 0x03 flg: 0x02 tail: 0xeb7b0603
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000D 0028EB7B 02030000 [........{.(.....]
011C6D410 00000000 00000001 0000D5C8 0028EB6F [............o.(.]
011C6D420 00000000 00320002 02000009 00240006 [......2.......$.]
011C6D430 0000065B 00800209 0016027F 00002001 [[............ ..]
011C6D440 0028EB7B 00000000 00000000 00000000 [{.(.............]
011C6D450 00000000 00000000 00000000 00000000 [................]
011C6D460 00000000 00010100 0014FFFF 1F7A1F8E [..............z.]
011C6D470 00001F7A 1F8E0001 00000000 00000000 [z...............]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
011C6F3F0 012C0000 726F0601 65676E61 EB7B0603 [..,...orange..{.] <= 保存的记录
Block header dump: 0x0200000d
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28eb6f itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.024.0000065b 0x00800209.027f.16 --U- 1 fsc 0x0000.0028eb7b <= insert 操作
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x01 0x0006.024.0000065b 0x00800209.027f.16 --U- 1 fsc 0x0000.0028eb7b <= insert 操作
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x11c6d464
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x11c6d464
bdba: 0x0200000d
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8e
avsp=0x1f7a
tosp=0x1f7a
0xe:pti[0] nrow=1 ffs=0
0x12:pri[0] ffs=0x1f8e <= 记录对应的物理位置
block_row_dump:
tab 0, row 0, @0x1f8e <= row#以及row#对应的记录的物理位置
tl: 10 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 6] 6f 72 61 6e 67 65 <= 记录
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x11c6d464
bdba: 0x0200000d
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8e
avsp=0x1f7a
tosp=0x1f7a
0xe:pti[0] nrow=1 ffs=0
0x12:pri[0] ffs=0x1f8e <= 记录对应的物理位置
block_row_dump:
tab 0, row 0, @0x1f8e <= row#以及row#对应的记录的物理位置
tl: 10 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 6] 6f 72 61 6e 67 65 <= 记录
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
----update操作
13:33:39
sys@prod(prod)> update test set a='apple';
1 row updated.
1 row updated.
13:34:09
sys@prod(prod)> commit;
Commit complete.
Commit complete.
select rowid,
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,
dbms_rowid.rowid_row_number(rowid) num ,
rowidtochar(rowid) from test where rownum=1
/
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,
dbms_rowid.rowid_row_number(rowid) num ,
rowidtochar(rowid) from test where rownum=1
/
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID)
------------------ ---------- ---------- ---------- ---------- ------------------
AAANXIAAIAAAAANAAA 54728 8 13 0 AAANXIAAIAAAAANAAA
------------------ ---------- ---------- ---------- ---------- ------------------
AAANXIAAIAAAAANAAA 54728 8 13 0 AAANXIAAIAAAAANAAA
13:35:08
sys@prod(prod)> alter system dump datafile 8 block 13;
System altered.
13:35:53
sys@prod(prod)> oradebug tracefile_name
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
15:00:54 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
15:00:54 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
Start dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
buffer tsn: 8 rdba: 0x0200000d (8/13)
scn: 0x0000.0028ec6b seq: 0x02 flg: 0x02 tail: 0xec6b0602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000D 0028EC6B 02020000 [........k.(.....]
011C6D410 00000000 00000001 0000D5C8 0028EC24 [............$.(.]
011C6D420 00000000 00320002 02000009 00240006 [......2.......$.]
011C6D430 0000065B 00800209 0016027F 00008000 [[...............]
011C6D440 0028EB7B 002B0002 00000639 00800A5F [{.(...+.9..._...]
011C6D450 000301B1 00012001 0028EC6B 00000000 [..... ..k.(.....]
011C6D460 00000000 00010100 0014FFFF 1F7A1F85 [..............z.]
011C6D470 00001F7B 1F850001 00000000 00000000 [{...............]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
011C6F3E0 00000000 00000000 01022C00 70706105 [.........,...app]
011C6F3F0 002C656C 726F0601 65676E61 EC6B0602 [le,...orange..k.] <=update之后新旧值都在块中,但是我们只能查到新值因为旧值已经被标记删除了
Block header dump: 0x0200000d
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28ec24 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
buffer tsn: 8 rdba: 0x0200000d (8/13)
scn: 0x0000.0028ec6b seq: 0x02 flg: 0x02 tail: 0xec6b0602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000D 0028EC6B 02020000 [........k.(.....]
011C6D410 00000000 00000001 0000D5C8 0028EC24 [............$.(.]
011C6D420 00000000 00320002 02000009 00240006 [......2.......$.]
011C6D430 0000065B 00800209 0016027F 00008000 [[...............]
011C6D440 0028EB7B 002B0002 00000639 00800A5F [{.(...+.9..._...]
011C6D450 000301B1 00012001 0028EC6B 00000000 [..... ..k.(.....]
011C6D460 00000000 00010100 0014FFFF 1F7A1F85 [..............z.]
011C6D470 00001F7B 1F850001 00000000 00000000 [{...............]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 501 times
011C6F3E0 00000000 00000000 01022C00 70706105 [.........,...app]
011C6F3F0 002C656C 726F0601 65676E61 EC6B0602 [le,...orange..k.] <=update之后新旧值都在块中,但是我们只能查到新值因为旧值已经被标记删除了
Block header dump: 0x0200000d
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28ec24 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0006.024.0000065b 0x00800209.027f.16 C--- 0 scn 0x0000.0028eb7b <= flag发生了改变
0x02 0x0002.02b.00000639 0x00800a5f.01b1.03 --U- 1 fsc 0x0001.0028ec6b <= update操作对以前的记录做了处理,同时插入新的数据到新的位置。
0x01 0x0006.024.0000065b 0x00800209.027f.16 C--- 0 scn 0x0000.0028eb7b <= flag发生了改变
0x02 0x0002.02b.00000639 0x00800a5f.01b1.03 --U- 1 fsc 0x0001.0028ec6b <= update操作对以前的记录做了处理,同时插入新的数据到新的位置。
data_block_dump,data header at 0x11c6d464
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x11c6d464
bdba: 0x0200000d
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f85
avsp=0x1f7a
tosp=0x1f7b
0xe:pti[0] nrow=1 ffs=0
0x12:pri[0] ffs=0x1f85 <=这个值发生了变化,row存储的物理位置发生了变化。由以前的offs=0x1f8e变成offs=0x1f85
block_row_dump:
tab 0, row 0, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 5] 61 70 70 6c 65
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x11c6d464
bdba: 0x0200000d
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f85
avsp=0x1f7a
tosp=0x1f7b
0xe:pti[0] nrow=1 ffs=0
0x12:pri[0] ffs=0x1f85 <=这个值发生了变化,row存储的物理位置发生了变化。由以前的offs=0x1f8e变成offs=0x1f85
block_row_dump:
tab 0, row 0, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 5] 61 70 70 6c 65
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
总结:update操作不影响rowid,但是新值和旧值都保存在数据块中。rowid中row#对应的物理位置发生了变化。update操作标记旧值为删除状态,在一个新的物理位置插入新值,同时改变原旧值对应的rowid的指向。
13:45:13
sys@prod(prod)> commit;
Commit complete.
Commit complete.
select rowid,
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,
dbms_rowid.rowid_row_number(rowid) num ,
rowidtochar(rowid) from test
/
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,
dbms_rowid.rowid_row_number(rowid) num ,
rowidtochar(rowid) from test
/
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID)
------------------ ---------- ---------- ---------- ---------- ------------------
AAANXIAAIAAAAANAAA 54728 8 13 0 AAANXIAAIAAAAANAAA
AAANXIAAIAAAAANAAB 54728 8 13 1 AAANXIAAIAAAAANAAB <=row# +1了,按照插入的记录的顺序递增。
------------------ ---------- ---------- ---------- ---------- ------------------
AAANXIAAIAAAAANAAA 54728 8 13 0 AAANXIAAIAAAAANAAA
AAANXIAAIAAAAANAAB 54728 8 13 1 AAANXIAAIAAAAANAAB <=row# +1了,按照插入的记录的顺序递增。
13:46:17
sys@prod(prod)> alter system dump datafile 8 block 13;
System altered.
System altered.
13:47:25
sys@prod(prod)> oradebug tracefile_name;
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
15:05:02 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
15:05:02 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
Start dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
buffer tsn: 8 rdba: 0x0200000d (8/13)
scn: 0x0000.0028ed13 seq: 0x02 flg: 0x02 tail: 0xed130602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000D 0028ED13 02020000 [..........(.....]
011C6D410 00000000 00000001 0000D5C8 0028EC24 [............$.(.]
011C6D420 00000000 00320002 02000009 0016000A [......2.........]
011C6D430 00000519 008005B6 001001DC 00002001 [............. ..]
011C6D440 0028ED13 002B0002 00000639 00800A5F [..(...+.9..._...]
011C6D450 000301B1 00012001 0028EC6B 00000000 [..... ..k.(.....]
011C6D460 00000000 00020100 0016FFFF 1F691F76 [............v.i.]
011C6D470 00001F6A 1F850002 00001F76 00000000 [j.......v.......]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 500 times
011C6F3D0 00000000 00000000 012C0000 65640B01 [..........,...de]
011C6F3E0 6574656C 776F7220 01022C73 70706105 [lete rows,...app] <= 块中存储的记录
011C6F3F0 002C656C 726F0601 65676E61 ED130602 [le,...orange....]
Block header dump: 0x0200000d
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28ec24 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
buffer tsn: 8 rdba: 0x0200000d (8/13)
scn: 0x0000.0028ed13 seq: 0x02 flg: 0x02 tail: 0xed130602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000D 0028ED13 02020000 [..........(.....]
011C6D410 00000000 00000001 0000D5C8 0028EC24 [............$.(.]
011C6D420 00000000 00320002 02000009 0016000A [......2.........]
011C6D430 00000519 008005B6 001001DC 00002001 [............. ..]
011C6D440 0028ED13 002B0002 00000639 00800A5F [..(...+.9..._...]
011C6D450 000301B1 00012001 0028EC6B 00000000 [..... ..k.(.....]
011C6D460 00000000 00020100 0016FFFF 1F691F76 [............v.i.]
011C6D470 00001F6A 1F850002 00001F76 00000000 [j.......v.......]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 500 times
011C6F3D0 00000000 00000000 012C0000 65640B01 [..........,...de]
011C6F3E0 6574656C 776F7220 01022C73 70706105 [lete rows,...app] <= 块中存储的记录
011C6F3F0 002C656C 726F0601 65676E61 ED130602 [le,...orange....]
Block header dump: 0x0200000d
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28ec24 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.016.00000519 0x008005b6.01dc.10 --U- 1 fsc 0x0000.0028ed13 <= 新的insert操作
0x02 0x0002.02b.00000639 0x00800a5f.01b1.03 --U- 1 fsc 0x0001.0028ec6b
data_block_dump,data header at 0x11c6d464
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x11c6d464
bdba: 0x0200000d
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f76
avsp=0x1f69
tosp=0x1f6a
0xe:pti[0] nrow=2 ffs=0
0x12:pri[0] ffs=0x1f85 <= 第一行记录的物理位置 pri后面的0应该就是rowid中的行号
0x14:pri[1] ffs=0x1f76 <= 第二行记录的物理位置
block_row_dump:
tab 0, row 0, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 5] 61 70 70 6c 65
tab 0, row 1, @0x1f76
tl: 15 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [11] 64 65 6c 65 74 65 20 72 6f 77 73
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
总结:insert操作会在一个新的物理位置保存新插入的记录并把row#指向这个物理位置
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x11c6d464
bdba: 0x0200000d
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f76
avsp=0x1f69
tosp=0x1f6a
0xe:pti[0] nrow=2 ffs=0
0x12:pri[0] ffs=0x1f85 <= 第一行记录的物理位置 pri后面的0应该就是rowid中的行号
0x14:pri[1] ffs=0x1f76 <= 第二行记录的物理位置
block_row_dump:
tab 0, row 0, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 5] 61 70 70 6c 65
tab 0, row 1, @0x1f76
tl: 15 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [11] 64 65 6c 65 74 65 20 72 6f 77 73
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
总结:insert操作会在一个新的物理位置保存新插入的记录并把row#指向这个物理位置
----delete操作----
14:19:37
sys@prod(prod)> select a,rowid from test;
A ROWID
-------------------- ------------------
apple AAANXIAAIAAAAANAAA
delete rows AAANXIAAIAAAAANAAB
-------------------- ------------------
apple AAANXIAAIAAAAANAAA
delete rows AAANXIAAIAAAAANAAB
14:19:53
sys@prod(prod)> delete from test where rowid='AAANXIAAIAAAAANAAB';
1 row deleted.
1 row deleted.
14:21:08
sys@prod(prod)> commit;
Commit complete.
Commit complete.
select rowid,
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,
dbms_rowid.rowid_row_number(rowid) num ,
rowidtochar(rowid) from test
/
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID)
------------------ ---------- ---------- ---------- ---------- ------------------
AAANXIAAIAAAAANAAA 54728 8 13 0 AAANXIAAIAAAAANAAA
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,
dbms_rowid.rowid_row_number(rowid) num ,
rowidtochar(rowid) from test
/
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID)
------------------ ---------- ---------- ---------- ---------- ------------------
AAANXIAAIAAAAANAAA 54728 8 13 0 AAANXIAAIAAAAANAAA
14:21:45
sys@prod(prod)> alter system dump datafile 8 block 13;
System altered.
14:22:03
sys@prod(prod)> oradebug tracefile_name;
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
15:09:15 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
15:09:15 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
Start dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
buffer tsn: 8 rdba: 0x0200000d (8/13)
scn: 0x0000.0028eda5 seq: 0x02 flg: 0x02 tail: 0xeda50602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000D 0028EDA5 02020000 [..........(.....]
011C6D410 00000000 00000001 0000D5C8 0028ED79 [............y.(.]
011C6D420 00000000 00320002 02000009 0016000A [......2.........]
011C6D430 00000519 008005B6 001001DC 00008000 [................]
011C6D440 0028ED13 002E0009 00000644 0080054B [..(.....D...K...]
011C6D450 00140225 000D2001 0028EDA5 00000000 [%.... ....(.....]
011C6D460 00000000 00020100 0016FFFF 1F6A1F76 [............v.j.]
011C6D470 00001F79 1F850002 00001F76 00000000 [y.......v.......]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 500 times
011C6F3D0 00000000 00000000 023C0000 65640B01 [..........<...de>011C6F3E0 6574656C 776F7220 01002C73 70706105 [lete rows,...app] <= 删除掉的那条记录依然保存在数据块中
011C6F3F0 002C656C 726F0601 65676E61 EDA50602 [le,...orange....]
Block header dump: 0x0200000d
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28ed79 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
buffer tsn: 8 rdba: 0x0200000d (8/13)
scn: 0x0000.0028eda5 seq: 0x02 flg: 0x02 tail: 0xeda50602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000D 0028EDA5 02020000 [..........(.....]
011C6D410 00000000 00000001 0000D5C8 0028ED79 [............y.(.]
011C6D420 00000000 00320002 02000009 0016000A [......2.........]
011C6D430 00000519 008005B6 001001DC 00008000 [................]
011C6D440 0028ED13 002E0009 00000644 0080054B [..(.....D...K...]
011C6D450 00140225 000D2001 0028EDA5 00000000 [%.... ....(.....]
011C6D460 00000000 00020100 0016FFFF 1F6A1F76 [............v.j.]
011C6D470 00001F79 1F850002 00001F76 00000000 [y.......v.......]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 500 times
011C6F3D0 00000000 00000000 023C0000 65640B01 [..........<...de>011C6F3E0 6574656C 776F7220 01002C73 70706105 [lete rows,...app] <= 删除掉的那条记录依然保存在数据块中
011C6F3F0 002C656C 726F0601 65676E61 EDA50602 [le,...orange....]
Block header dump: 0x0200000d
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28ed79 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.016.00000519 0x008005b6.01dc.10 C--- 0 scn 0x0000.0028ed13 <= 再次看到flag C,看来C代表的是delete操作或者是标记这条记录为删除状态。重用了事务槽?
0x02 0x0009.02e.00000644 0x0080054b.0225.14 --U- 1 fsc 0x000d.0028eda5
0x01 0x000a.016.00000519 0x008005b6.01dc.10 C--- 0 scn 0x0000.0028ed13 <= 再次看到flag C,看来C代表的是delete操作或者是标记这条记录为删除状态。重用了事务槽?
0x02 0x0009.02e.00000644 0x0080054b.0225.14 --U- 1 fsc 0x000d.0028eda5
data_block_dump,data header at 0x11c6d464
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x11c6d464
bdba: 0x0200000d
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f76
avsp=0x1f6a
tosp=0x1f79
0xe:pti[0] nrow=2 ffs=0
0x12:pri[0] ffs=0x1f85
0x14:pri[1] ffs=0x1f76 <= 删除的记录对应的这个row#还在,物理地址和还是那条记录的。
block_row_dump:
tab 0, row 0, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 5] 61 70 70 6c 65
tab 0, row 1, @0x1f76 <= row#虽然在但是已经没有记录了
tl: 2 fb: --HDFL-- lb: 0x2
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x11c6d464
bdba: 0x0200000d
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f76
avsp=0x1f6a
tosp=0x1f79
0xe:pti[0] nrow=2 ffs=0
0x12:pri[0] ffs=0x1f85
0x14:pri[1] ffs=0x1f76 <= 删除的记录对应的这个row#还在,物理地址和还是那条记录的。
block_row_dump:
tab 0, row 0, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 5] 61 70 70 6c 65
tab 0, row 1, @0x1f76 <= row#虽然在但是已经没有记录了
tl: 2 fb: --HDFL-- lb: 0x2
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
A ROWID
-------------------- ------------------
apple AAANXIAAIAAAAANAAA
-------------------- ------------------
apple AAANXIAAIAAAAANAAA
14:31:18
sys@prod(prod)> insert into test values('new insert');
1 row created.
14:31:35
sys@prod(prod)> commit;
Commit complete.
14:31:38
sys@prod(prod)> select a,rowid from test;
A ROWID
-------------------- ------------------
apple AAANXIAAIAAAAANAAA
new insert AAANXIAAIAAAAANAAC <= 新插入的记录的rowid是C,跳过了我们删除掉的那个记录的rowid,看来删除后不会马上重用rowid
-------------------- ------------------
apple AAANXIAAIAAAAANAAA
new insert AAANXIAAIAAAAANAAC <= 新插入的记录的rowid是C,跳过了我们删除掉的那个记录的rowid,看来删除后不会马上重用rowid
14:31:42
sys@prod(prod)> alter system dump datafile 8 block 13;
System altered.
14:33:32
sys@prod(prod)> oradebug tracefile_name;
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
15:12:56 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
15:12:56 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
Start dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
buffer tsn: 8 rdba: 0x0200000d (8/13)
scn: 0x0000.0028ee08 seq: 0x02 flg: 0x02 tail: 0xee080602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000D 0028EE08 02020000 [..........(.....]
011C6D410 00000000 00000001 0000D5C8 0028ED79 [............y.(.]
011C6D420 00000000 00320002 02000009 00290003 [......2.......).]
011C6D430 0000065D 00800199 00140205 00002001 []............ ..]
011C6D440 0028EE08 002E0009 00000644 0080054B [..(.....D...K...]
011C6D450 00140225 000D2001 0028EDA5 00000000 [%.... ....(.....]
011C6D460 00000000 00030100 0018FFFF 1F5A1F68 [............h.Z.]
011C6D470 00001F69 1F850003 1F681F76 00000000 [i.......v.h.....]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 499 times
011C6F3C0 00000000 00000000 00000000 0A01012C [............,...]
011C6F3D0 2077656E 65736E69 023C7472 65640B01 [new insert<...de>011C6F3E0 6574656C 776F7220 01002C73 70706105 [lete rows,...app]
011C6F3F0 002C656C 726F0601 65676E61 EE080602 [le,...orange....]
Block header dump: 0x0200000d
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28ed79 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
buffer tsn: 8 rdba: 0x0200000d (8/13)
scn: 0x0000.0028ee08 seq: 0x02 flg: 0x02 tail: 0xee080602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000D 0028EE08 02020000 [..........(.....]
011C6D410 00000000 00000001 0000D5C8 0028ED79 [............y.(.]
011C6D420 00000000 00320002 02000009 00290003 [......2.......).]
011C6D430 0000065D 00800199 00140205 00002001 []............ ..]
011C6D440 0028EE08 002E0009 00000644 0080054B [..(.....D...K...]
011C6D450 00140225 000D2001 0028EDA5 00000000 [%.... ....(.....]
011C6D460 00000000 00030100 0018FFFF 1F5A1F68 [............h.Z.]
011C6D470 00001F69 1F850003 1F681F76 00000000 [i.......v.h.....]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 499 times
011C6F3C0 00000000 00000000 00000000 0A01012C [............,...]
011C6F3D0 2077656E 65736E69 023C7472 65640B01 [new insert<...de>011C6F3E0 6574656C 776F7220 01002C73 70706105 [lete rows,...app]
011C6F3F0 002C656C 726F0601 65676E61 EE080602 [le,...orange....]
Block header dump: 0x0200000d
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28ed79 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.029.0000065d 0x00800199.0205.14 --U- 1 fsc 0x0000.0028ee08
0x02 0x0009.02e.00000644 0x0080054b.0225.14 --U- 1 fsc 0x000d.0028eda5
0x01 0x0003.029.0000065d 0x00800199.0205.14 --U- 1 fsc 0x0000.0028ee08
0x02 0x0009.02e.00000644 0x0080054b.0225.14 --U- 1 fsc 0x000d.0028eda5
data_block_dump,data header at 0x11c6d464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x11c6d464
bdba: 0x0200000d
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f68
avsp=0x1f5a
tosp=0x1f69
0xe:pti[0] nrow=3 ffs=0
0x12:pri[0] ffs=0x1f85
0x14:pri[1] ffs=0x1f76
0x16:pri[2] ffs=0x1f68 <= 出现了新的记录,新的row#
block_row_dump:
tab 0, row 0, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 5] 61 70 70 6c 65
tab 0, row 1, @0x1f76 <= 删除掉的记录的rowid还在,没有被重用,这就是碎片产生的原因,也是可以把删除的数据恢复的关键。
tl: 2 fb: --HDFL-- lb: 0x2 <= 注意 fb:多了个D 变成fb: --HDFL--了
tab 0, row 2, @0x1f68 <= 新插入的数据的row#以及所对应的物理位置。
tl: 14 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [10] 6e 65 77 20 69 6e 73 65 72 74
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x11c6d464
bdba: 0x0200000d
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f68
avsp=0x1f5a
tosp=0x1f69
0xe:pti[0] nrow=3 ffs=0
0x12:pri[0] ffs=0x1f85
0x14:pri[1] ffs=0x1f76
0x16:pri[2] ffs=0x1f68 <= 出现了新的记录,新的row#
block_row_dump:
tab 0, row 0, @0x1f85
tl: 9 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 5] 61 70 70 6c 65
tab 0, row 1, @0x1f76 <= 删除掉的记录的rowid还在,没有被重用,这就是碎片产生的原因,也是可以把删除的数据恢复的关键。
tl: 2 fb: --HDFL-- lb: 0x2 <= 注意 fb:多了个D 变成fb: --HDFL--了
tab 0, row 2, @0x1f68 <= 新插入的数据的row#以及所对应的物理位置。
tl: 14 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [10] 6e 65 77 20 69 6e 73 65 72 74
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
insert操作产生新的rowid。update操作不会改变rowid,但是rowid对应的记录的物理位置和以前不同了。delete操作把对应的rowid置空,只有指向没有具体的值。
rowid中的row#以及其他rowid的部分应该是逻辑的存在,数据库定义的数据结构,不会因为物理位置的变化而发生变化。记录的rowid发生变化,只是记录存储的物理位置对应到了其他row#指针上,row#本身不变。
15:16:10
sys@prod(prod)> alter table test shrink space;
Table altered.
Table altered.
select rowid,
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,
dbms_rowid.rowid_row_number(rowid) num ,
rowidtochar(rowid) from test
/
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID)
------------------ ---------- ---------- ---------- ---------- ------------------
AAANXIAAIAAAAAMAAA 54728 8 12 0 AAANXIAAIAAAAAMAAA
AAANXIAAIAAAAAMAAB 54728 8 12 1 AAANXIAAIAAAAAMAAB
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,
dbms_rowid.rowid_row_number(rowid) num ,
rowidtochar(rowid) from test
/
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID)
------------------ ---------- ---------- ---------- ---------- ------------------
AAANXIAAIAAAAAMAAA 54728 8 12 0 AAANXIAAIAAAAAMAAA
AAANXIAAIAAAAAMAAB 54728 8 12 1 AAANXIAAIAAAAAMAAB
以前的情况
14:31:38 sys@prod(prod)> select a,rowid from test;
14:31:38 sys@prod(prod)> select a,rowid from test;
A ROWID
-------------------- ------------------
apple AAANXIAAIAAAAANAAA
new insert AAANXIAAIAAAAANAAC
-------------------- ------------------
apple AAANXIAAIAAAAANAAA
new insert AAANXIAAIAAAAANAAC
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM ROWIDTOCHAR(ROWID)
------------------ ---------- ---------- ---------- ---------- ------------------
AAANXIAAIAAAAANAAA 54728 8 13 0 AAANXIAAIAAAAANAAA
------------------ ---------- ---------- ---------- ---------- ------------------
AAANXIAAIAAAAANAAA 54728 8 13 0 AAANXIAAIAAAAANAAA
从id为13的块,移动到了id为12的块
dump这两个块
dump第13个块
15:16:27 sys@prod(prod)> alter system dump datafile 8 block 13;
System altered.
dump这两个块
dump第13个块
15:16:27 sys@prod(prod)> alter system dump datafile 8 block 13;
System altered.
15:16:49
sys@prod(prod)> oradebug tracefile_name
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
15:17:01 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
Start dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
buffer tsn: 8 rdba: 0x0200000d (8/13)
scn: 0x0000.0028f088 seq: 0x01 flg: 0x06 tail: 0xf0880601
frmt: 0x02 chkval: 0xaa97 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000D 0028F088 06010000 [..........(.....]
011C6D410 0000AA97 00000001 0000D5C8 0028F086 [..............(.]
011C6D420 00000000 10320002 02000009 00290003 [......2.......).]
011C6D430 0000065D 00800199 00140205 00008000 []...............]
011C6D440 0028EE08 000F0007 00000567 00800C48 [..(.....g...H...]
011C6D450 00030344 00132002 0028F088 00000000 [D.... ....(.....]
011C6D460 00000000 00030100 00180001 1F691F68 [............h.i.]
011C6D470 00001F80 1F850003 1F68FFFF 00000000 [..........h.....]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 499 times
011C6F3C0 00000000 00000000 00000000 0A01023C [............<...>011C6F3D0 2077656E 65736E69 023C7472 65640B01 [new insert<...de>011C6F3E0 6574656C 776F7220 01023C73 70706105 [lete rows<...app>011C6F3F0 002C656C 726F0601 65676E61 F0880601 [le,...orange....]
Block header dump: 0x0200000d
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28f086 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 16
inc: 0 exflg: 0
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
15:17:01 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
Start dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
buffer tsn: 8 rdba: 0x0200000d (8/13)
scn: 0x0000.0028f088 seq: 0x01 flg: 0x06 tail: 0xf0880601
frmt: 0x02 chkval: 0xaa97 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000D 0028F088 06010000 [..........(.....]
011C6D410 0000AA97 00000001 0000D5C8 0028F086 [..............(.]
011C6D420 00000000 10320002 02000009 00290003 [......2.......).]
011C6D430 0000065D 00800199 00140205 00008000 []...............]
011C6D440 0028EE08 000F0007 00000567 00800C48 [..(.....g...H...]
011C6D450 00030344 00132002 0028F088 00000000 [D.... ....(.....]
011C6D460 00000000 00030100 00180001 1F691F68 [............h.i.]
011C6D470 00001F80 1F850003 1F68FFFF 00000000 [..........h.....]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 499 times
011C6F3C0 00000000 00000000 00000000 0A01023C [............<...>011C6F3D0 2077656E 65736E69 023C7472 65640B01 [new insert<...de>011C6F3E0 6574656C 776F7220 01023C73 70706105 [lete rows<...app>011C6F3F0 002C656C 726F0601 65676E61 F0880601 [le,...orange....]
Block header dump: 0x0200000d
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28f086 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 16
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0003.029.0000065d 0x00800199.0205.14 C--- 0 scn 0x0000.0028ee08
0x02 0x0007.00f.00000567 0x00800c48.0344.03 --U- 2 fsc 0x0013.0028f088
0x01 0x0003.029.0000065d 0x00800199.0205.14 C--- 0 scn 0x0000.0028ee08
0x02 0x0007.00f.00000567 0x00800c48.0344.03 --U- 2 fsc 0x0013.0028f088
data_block_dump,data header at 0x11c6d464
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x11c6d464
bdba: 0x0200000d
76543210
flag=--------
ntab=1
nrow=3
frre=1
fsbo=0x18
fseo=0x1f68
avsp=0x1f69
tosp=0x1f80
0xe:pti[0] nrow=3 ffs=0
0x12:pri[0] ffs=0x1f85
0x14:pri[1] sfll=-1
0x16:pri[2] ffs=0x1f68
block_row_dump:
tab 0, row 0, @0x1f85
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 2, @0x1f68
tl: 2 fb: --HDFL-- lb: 0x2
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x11c6d464
bdba: 0x0200000d
76543210
flag=--------
ntab=1
nrow=3
frre=1
fsbo=0x18
fseo=0x1f68
avsp=0x1f69
tosp=0x1f80
0xe:pti[0] nrow=3 ffs=0
0x12:pri[0] ffs=0x1f85
0x14:pri[1] sfll=-1
0x16:pri[2] ffs=0x1f68
block_row_dump:
tab 0, row 0, @0x1f85
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 2, @0x1f68
tl: 2 fb: --HDFL-- lb: 0x2
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 13 maxblk 13
15:19:50
sys@prod(prod)> alter system dump datafile 8 block 12;
System altered.
15:23:53
sys@prod(prod)> oradebug tracefile_name
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
15:24:03 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
Start dump data blocks tsn: 8 file#: 8 minblk 12 maxblk 12
buffer tsn: 8 rdba: 0x0200000c (8/12)
scn: 0x0000.0028f088 seq: 0x01 flg: 0x06 tail: 0xf0880601
frmt: 0x02 chkval: 0xffa3 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000C 0028F088 06010000 [..........(.....]
011C6D410 0000FFA3 00000001 0000D5C8 0028EB6F [............o.(.]
011C6D420 00000000 00320002 02000009 000F0007 [......2.........]
011C6D430 00000567 00800C48 00050344 00002002 [g...H...D.... ..]
011C6D440 0028F088 00000000 00000000 00000000 [..(.............]
011C6D450 00000000 00000000 00000000 00000000 [................]
011C6D460 00000000 00020100 0016FFFF 1F6B1F81 [..............k.]
011C6D470 00001F6B 1F8F0002 00001F81 00000000 [k...............]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 499 times
011C6F3C0 00000000 00000000 00000000 0A01012C [............,...]
011C6F3D0 2077656E 65736E69 023C7472 65640B01 [new insert<...de>011C6F3E0 6574656C 01012C20 77656E0A 736E6920 [lete ,...new ins]
011C6F3F0 2C747265 61050101 656C7070 F0880601 [ert,...apple....]
Block header dump: 0x0200000c
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28eb6f itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
/u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
15:24:03 sys@prod(prod)> !vi /u01/app/oracle/admin/prod/udump/prod_ora_26336.trc
Start dump data blocks tsn: 8 file#: 8 minblk 12 maxblk 12
buffer tsn: 8 rdba: 0x0200000c (8/12)
scn: 0x0000.0028f088 seq: 0x01 flg: 0x06 tail: 0xf0880601
frmt: 0x02 chkval: 0xffa3 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000011C6D400 to 0x0000000011C6F400
011C6D400 0000A206 0200000C 0028F088 06010000 [..........(.....]
011C6D410 0000FFA3 00000001 0000D5C8 0028EB6F [............o.(.]
011C6D420 00000000 00320002 02000009 000F0007 [......2.........]
011C6D430 00000567 00800C48 00050344 00002002 [g...H...D.... ..]
011C6D440 0028F088 00000000 00000000 00000000 [..(.............]
011C6D450 00000000 00000000 00000000 00000000 [................]
011C6D460 00000000 00020100 0016FFFF 1F6B1F81 [..............k.]
011C6D470 00001F6B 1F8F0002 00001F81 00000000 [k...............]
011C6D480 00000000 00000000 00000000 00000000 [................]
Repeat 499 times
011C6F3C0 00000000 00000000 00000000 0A01012C [............,...]
011C6F3D0 2077656E 65736E69 023C7472 65640B01 [new insert<...de>011C6F3E0 6574656C 01012C20 77656E0A 736E6920 [lete ,...new ins]
011C6F3F0 2C747265 61050101 656C7070 F0880601 [ert,...apple....]
Block header dump: 0x0200000c
Object id on Block? Y
seg/obj: 0xd5c8 csc: 0x00.28eb6f itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0007.00f.00000567 0x00800c48.0344.05 --U- 2 fsc 0x0000.0028f088
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x01 0x0007.00f.00000567 0x00800c48.0344.05 --U- 2 fsc 0x0000.0028f088
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x11c6d464
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x11c6d464
bdba: 0x0200000c
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f81
avsp=0x1f6b
tosp=0x1f6b
0xe:pti[0] nrow=2 ffs=0
0x12:pri[0] ffs=0x1f8f
0x14:pri[1] ffs=0x1f81
block_row_dump:
tab 0, row 0, @0x1f8f
tl: 9 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 5] 61 70 70 6c 65
tab 0, row 1, @0x1f81
tl: 14 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [10] 6e 65 77 20 69 6e 73 65 72 74
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 12 maxblk 12
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x11c6d464
bdba: 0x0200000c
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f81
avsp=0x1f6b
tosp=0x1f6b
0xe:pti[0] nrow=2 ffs=0
0x12:pri[0] ffs=0x1f8f
0x14:pri[1] ffs=0x1f81
block_row_dump:
tab 0, row 0, @0x1f8f
tl: 9 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 5] 61 70 70 6c 65
tab 0, row 1, @0x1f81
tl: 14 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [10] 6e 65 77 20 69 6e 73 65 72 74
end_of_block_dump
End dump data blocks tsn: 8 file#: 8 minblk 12 maxblk 12
记录从13号块移动到了12号块并且重新进行了排列。rowid中的block#和row#都发生了变化。shrink的操作影响还是比较大的。把13号块中删除的值也移动了过来,就像对数据部分进行了dd一样
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14614169/viewspace-751064/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14614169/viewspace-751064/