Oracle Study之案例--Oracle ASSM管理方式下的Bitmap
在基于此在LMT(Extent Local Management）下Oracle建议我们使用ASSM（Automatic Segment-Space Management），看看
Oracle doc是如何来解释ASSM的：
This keyword tells Oracle that you want to use bitmaps to manage the free space with in segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps enable Oracle to manage free space more automatically; thus, this form of space management is called automatic segment-space management. Locally managed tablespaces using automatic segment-space management can be created as smallfile (traditional) or bigfile tablespaces. AUTO is the default.
在Oracle 10g，在本地管理的表空间中，如果数据库块大小（db_block_size）为16KB或16KB以下，数据文件头保留64KB空间；若是32KB的块大小，则保留128KB。即数据块大小和文件头保留的数据块块数之间的对应关系为：
db_block_size=2 KB，文件头会保留32个数据块；
db_block_size=4 KB，文件头会保留16个数据块；
db_block_size=8 KB，文件头会保留8个数据块；
db_block_size=16 KB，文件头会保留4个数据块；
db_block_size=32 KB，文件头会保留4个数据块。
对于默认8KB块大小的表空间，Oracle保留8个块用于管理，其中数据块1和2用于记录数据文件头信息，数据块3～8用于记录区间的位图信息。
Oracle 11G:0号操作系统块，1-2是文件头，3-127是位图信息。128号开始及之后存放的是数据了—可能是段头或段的数据。
案例：
t2表是test2表空间的第一个segment：
11:24:30 SYS@ test1 >col owner for a10
11:24:43 SYS@ test1 >select OWNER,SEGMENT_NAME,TABLESPACE_NAME,FILE_ID,EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents
2* where tablespace_name='TEST1'
OWNER SEGMENT_NAME TABLESPACE_NAME FILE_ID EXTENT_ID BLOCK_ID BLOCKS
---------- -------------------- ------------------------------ ---------- ---------- ---------- ----------
SCOTT T2 TEST2 8 0 128 8
11:10:57 SYS@ test1 >alter system dump datafile 3 block min 1 block max 127;
System altered.DUMP文件（1#块为datafile header）
Start dump data blocks tsn: 22 file#:3 minblk 1 maxblk 127
Block 1 (file header) not dumped:use dump file header command
Block dump from cache:
Dump of buffer cache at level 4 for tsn=22, rdba=12582914
BH (0x227e9254) file#: 3 rdba: 0x00c00002 (3/2) class: 13 ba: 0x22516000
set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
dbwrid: 0 obj: -1 objn: 3 tsn: 22 afn: 3 hint: f
hash: [0x2b7af910,0x2b7af910] lru: [0x227e93d4,0x227e9158]
lru-flags: hot_buffer
obj-flags: object_ckpt_list
ckptq: [0x223e3ddc,0x2bbe3ba4] fileq: [0x223e3de4,0x2bbe3be0] objq: [0x27fa8800,0x223e4b0c]
st: XCURRENT md: NULL tch: 0
flags: buffer_dirty redo_since_read
LRBA: [0x34.188.0] LSCN: [0x0.a2349c] HSCN: [0x0.a2349c] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 22 rdba: 0x00c00002 (3/2)
scn: 0x0000.0096c8a5 seq: 0x02 flg: 0x04 tail: 0xc8a51d02
frmt: 0x02 chkval: 0x72e8 type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00758200 to 0x0075A200
758200 0000A21D 00C00002 0096C8A5 04020000 [................]
758210 000072E8 00000003 00000008 00000500 [.r..............]
758220 00000001 00000000 00000000 0000007E [............~...]
758230 000004FF 00000000 00000090 0096C8A4 [................]
758240 00000000 00000000 00000000 00000000 [................]
758250 00000080 00000008 00000000 00000000 [................]
758260 00000000 00000000 00000000 00000000 [................]
Repeat 504 times
75A1F0 00000000 00000000 00000000 C8A51D02 [................]
File Space Header Block:
Header Control:
RelFno: 3, Unit: 8, Size: 1280, Flag: 1
AutoExtend: NO, Increment: 0, MaxSize: 0
Initial Area: 126, Tail: 1279, First: 0, Free: 144
Deallocation scn: 9881764.0
Header Opcode:
Save: No Pending Opblock# 127:
Block dump from cache:
Dump of buffer cache at level 4 for tsn=22, rdba=12583039
Block dump from disk:
buffer tsn: 22 rdba: 0x00c0007f (3/127)
scn: 0x0000.0090e601 seq: 0x01 flg: 0x04 tail: 0xe6011e01
frmt: 0x02 chkval: 0x4373 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00758200 to 0x0075A200
758200 0000A21E 00C0007F 0090E601 04010000 [................]
758210 00004373 00000003 03C10080 00000000 [sC..............]
758220 00000000 0000F800 00000000 00000000 [................]
758230 00000000 00000000 00000000 00000000 [................]
Repeat 507 times
75A1F0 00000000 00000000 00000000 E6011E01 [................]
File Space Bitmap Block:
BitMap Control:
RelFno: 3, BeginBlock: 62980224, Flag: 0, First: 0, Free: 63488
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000案例分析：
1、查看emp表存储信息
17:31:39 SYS@ test1 >col segment_name for a20
17:33:01 SYS@ test1 >select owner,segment_name,segment_type,tablespace_name,bytes/1024 from dba_segments
17:33:24 2 where segment_name='EMP';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024
------------------------------ -------------------- ------------------ ------------------------------ ----------
SCOTT EMP TABLE USERS
64
17:31:06 SYS@ test1 >select tablespace_name,contents,extent_management,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
TABLESPACE_NAME CONTENTS EXTENT_MAN SEGMEN
------------------------------ --------- ---------- ------
SYSTEM PERMANENT DICTIONARY MANUAL
USERS PERMANENT LOCAL AUTO
17:37:42 SYS@ test1 >COL OWNER FOR A10
17:37:54 SYS@ test1 >R
1 select owner,SEGMENT_NAME,EXTENT_ID, FILE_ID,BLOCK_ID,BLOCKS from dba_extents
2* where segment_name='EMP'
OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- -------------------------------------------------- ---------- ---------- ---------- ----------
SCOTT EMP 0 4 144 8
17:16:05 SYS@ test1 >select header_file,header_block,blocks,extents from dba_segments where segment_name='EMP';
HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
----------- ------------ ---------- ----------
4 146 8 1从以上可以看到，emp表存储在users表空间上，users表空间采用LMT管理；所以对于emp表，其segment space采用auto方式。起始block为144，segment header block为146.
2、对emp表中的block进行dump分析
17:39:30 SYS@ test1 >alter system dump datafile 4 block min 144 block max 151;
System altered.
查看dump的trace文件：
[oracle@RH6 ~]$ ls -lt /u01/app/oracle/diag/rdbms/test1/test1/trace/|more
total 56908
-rw-r----- 1 oracle oinstall 25923 Dec 15 17:42 test1_ora_2385.trc
[oracle@RH6 ~]$ more /u01/app/oracle/diag/rdbms/test1/test1/trace/test1_ora_2385.trc
Trace file /u01/app/oracle/diag/rdbms/test1/test1/trace/test1_ora_2385.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: rh7.cuug.net
Release: 2.6.32-71.el6.i686
Version: #1 SMP Wed Sep 1 01:26:34 EDT 2010
Machine: i686
Instance name: test1
Redo thread mounted by this instance: 1
Oracle process number: 0
Unix process pid: 2385, p_w_picpath: oracle@rh7.cuug.net
*** 2014-04-22 10:33:45.255
Breaking the connection before proto/dty negotiation, error raised 3113
Trace file /u01/app/oracle/diag/rdbms/test1/test1/trace/test1_ora_2385.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: RH6.cuug.net
Release: 2.6.32-71.el6.i686
Version: #1 SMP Wed Sep 1 01:26:34 EDT 2010
Machine: i686
Instance name: test1
Redo thread mounted by this instance: 1
Oracle process number: 17
Unix process pid: 2385, p_w_picpath: oracle@RH6.cuug.net (TNS V1-V3)
*** 2014-12-15 17:35:47.781
*** SESSION ID:(1.5) 2014-12-15 17:35:47.781
*** CLIENT ID:() 2014-12-15 17:35:47.781
*** SERVICE NAME:() 2014-12-15 17:35:47.781
*** MODULE NAME:(sqlplus@RH6.cuug.net (TNS V1-V3)) 2014-12-15 17:35:47.781
*** ACTION NAME:() 2014-12-15 17:35:47.781
Thread 1 checkpoint: logseq 41, block 2, scn 10122512
cache-low rba: logseq 41, block 7060
on-disk rba: logseq 41, block 7085, scn 10126253
change track rba: logseq 41, block 7085, scn 10126337
start recovery at logseq 41, block 7060, scn 0
*** 2014-12-15 17:35:47.995
Started writing zeroblks thread 1 seq 41 blocks 7085-7092
*** 2014-12-15 17:35:48.030
Completed writing zeroblks thread 1 seq 41
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 4096Kb
-- Redo read_disk statistics --
Read rate (ASYNC): 12Kb in 0.25s => 0.05 Mb/sec
Longest record: 1Kb, moves: 0/41 (0%)
Change moves: 1/11 (9%), moved: 0Mb
Longest LWN: 9Kb, moves: 0/3 (0%), moved: 0Mb
Last redo scn: 0x0000.009a83ac (10126252)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 25/25 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 30/67 = 0.4
----------------------------------------------
*** 2014-12-15 17:35:48.085
KCRA: start recovery claims for 25 data blocks
*** 2014-12-15 17:35:48.256
KCRA: blocks processed = 25/25, claimed = 25, eliminated = 0
*** 2014-12-15 17:35:48.257
Recovery of Online Redo Log: Thread 1 Group 4 Seq 41 Reading mem 0
*** 2014-12-15 17:35:48.382
Completed redo application of 0.01MB
*** 2014-12-15 17:35:50.522
Completed recovery checkpoint
IR RIA: redo_size 12288 bytes, time_taken 267 ms
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 25/25 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 47/55 = 0.9
----------------------------------------------
Recovery sets nab of thread 1 seq 41 to 7085 with 8 zeroblks
*** 2014-12-15 17:36:09.120
kwqmnich: current time:: 9: 36: 8: 0
kwqmnich: instance no 0 repartition flag 1
kwqmnich: initialized job cache structure
kwqinfy: Call kwqrNondurSubInstTsk
*** 2014-12-15 17:42:08.722
Start dump data blocks tsn: 4 file#:4 minblk 144 maxblk 151
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777360Block dump from disk:
buffer tsn: 4 rdba: 0x01000090 (4/144)
scn: 0x0000.0096c992 seq: 0x02 flg: 0x04 tail: 0xc9922002
frmt: 0x02 chkval: 0x4bc8 type: 0x20=FIRST LEVEL BITMAP BLOCK
其中在第144个block的dump信息中我们发现type: 0x20=FIRST LEVEL BITMAP BLOCK，说明这个block是第一级bitmap block，Oracle用第一级bitmap block来管理data block的使用情况：
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x008E8200 to 0x008EA200
8E8200 0000A220 01000090 0096C992 04020000 [ ...............]
8E8210 00004BC8 00000000 00000000 00000000 [.K..............]
8E8220 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
8E8240 00000000 00000000 00000000 00000004 [................]
8E8250 FFFFFFFF 00000000 00000003 00000008 [................]
8E8260 00010001 00000000 00000000 00000000 [................]
8E8270 00000005 00000003 54682010 54682010 [......... hT. hT]
8E8280 00000000 00000000 00000000 00000000 [................]
8E8290 01000091 00000000 00000000 00000008 [................]
8E82A0 00000008 01000098 00000000 00000000 [................]
8E82B0 00000000 00000005 00000000 00000001 [................]
8E82C0 000044A3 0096C903 00000000 01000090 [.D..............]
8E82D0 00000008 00000000 00000000 00000000 [................]
8E82E0 00000000 00000000 00000000 00000000 [................]
Repeat 9 times
8E8380 00000000 00000000 00000000 55551511 [..............UU]
8E8390 00000000 00000000 00000000 00000000 [................]
Repeat 485 times
8EA1F0 00000000 00000000 00000000 C9922002 [............. ..]
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x01000091 poffset: 0
unformatted: 0 total: 8 first useful block: 3
owning instance : 1
instance ownership changed at 11/16/2014 11:54:56
Last successful Search 11/16/2014 11:54:56
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 5
Extent Map Block Offset: 4294967295
First free datablock : 3
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Inc #: 0 Objd: 17571
HWM Flag: HWM Set
Highwater:: 0x01000098 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x01000090 Length: 8 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:75-100% free
4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free
--------------------------------------------------------
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777361Block dump from disk:
buffer tsn: 4 rdba: 0x01000091 (4/145)
scn: 0x0000.0096c97a seq: 0x02 flg: 0x04 tail: 0xc97a2102
frmt: 0x02 chkval: 0xc282 type: 0x21=SECOND LEVEL BITMAP BLOCK
145＃block的类型是：type: 0x21=SECOND LEVEL BITMAP BLOCK，说明该block是二级bitmap block，他是用来管理一级bitmap block的。
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x008E8200 to 0x008EA200
8E8200 0000A221 01000091 0096C97A 04020000 [!.......z.......]
8E8210 0000C282 00000000 00000000 00000000 [................]
8E8220 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
8E8240 00000000 00000000 00000000 01000092 [................]
8E8250 00000001 00000001 00000000 00000000 [................]
8E8260 00000000 00000000 000044A3 00000001 [.........D......]
8E8270 00000000 01000090 00010005 00000000 [................]
8E8280 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
8EA1F0 00000000 00000000 00000000 C97A2102 [.............!z.]
Dump of Second Level Bitmap Block
number: 1 nfree: 1 ffree: 0 pdba: 0x01000092
Inc #: 0 Objd: 17571
opcode:0
xid:
L1 Ranges :
--------------------------------------------------------
0x01000090 Free: 5 Inst: 1
--------------------------------------------------------
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777362
BH (0x23bfb924) file#: 4 rdba: 0x01000092 (4/146) class: 4 ba: 0x23bde000
set: 2 pool 2 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 17571 objn: -1 tsn: 4 afn: 4 hint: f
hash: [0x23bfba74,0x2b7af538] lru: [0x23bfbaa4,0x2bbdd3b4]
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.9ad2c6],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.9ad2c6],[sfl: 0x0],[lc: 0x0.0]
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x23bfb9f8) file#: 4 rdba: 0x01000092 (4/146) class: 4 ba: 0x23be0000
set: 2 pool 2 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 17571 objn: -1 tsn: 4 afn: 4 hint: f
hash: [0x2b7af538,0x23bfb9a0] lru: [0x2bbdd3b4,0x23bfb9d0]
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.9ad2c1],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.9ad2c1],[sfl: 0x0],[lc: 0x0.0]
flags:
cr pin refcnt: 0 sh pin refcnt: 0Block dump from disk:
buffer tsn: 4 rdba: 0x01000092 (4/146)
scn: 0x0000.0096c992 seq: 0x03 flg: 0x04 tail: 0xc9922303
frmt: 0x02 chkval: 0xefbe type: 0x23=PAGETABLE SEGMENT HEADER
接下来简单看看146＃block的类型是：
type: 0x23=PAGETABLE SEGMENT HEADER
也就是我们熟悉的segment header block，这里它也同时是一个特殊的三级bitmap block
同时最下面一条非常有用的信息是：
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000091
记录了二级btimap block的地址，因为segment小，只有1个二级bitmap block，大家也可以创建一个大一
点的表，主要需要保证要有足够多的block才可能看到第二个二级block出现，要看到出现一个三级bitmap
block可能非常的难。
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x008E8200 to 0x008EA200
8E8200 0000A223 01000092 0096C992 04030000 [#...............]
8E8210 0000EFBE 00000000 00000000 00000000 [................]
8E8220 00000000 00000001 00000008 00000A9C [................]
8E8230 00000000 00000008 00000008 01000098 [................]
8E8240 00000000 00000000 00000000 00000005 [................]
8E8250 00000000 00000000 00000000 00000000 [................]
8E8260 00000008 00000008 01000098 00000000 [................]
8E8270 00000000 00000000 00000005 01000090 [................]
8E8280 01000090 00000000 00000000 00000000 [................]
8E8290 00000000 00000000 00000000 00000000 [................]
Repeat 3 times
8E82D0 00000001 00002000 00000000 00001434 [..... ......4...]
8E82E0 00000000 01000091 00000001 01000090 [................]
8E82F0 01000091 00000000 00000000 00000000 [................]
8E8300 00000000 00000000 00000001 00000000 [................]
8E8310 000044A3 10000000 01000090 00000008 [.D..............]
8E8320 00000000 00000000 00000000 00000000 [................]
Repeat 152 times
8E8CB0 01000090 01000093 00000000 00000000 [................]
8E8CC0 00000000 00000000 00000000 00000000 [................]
Repeat 151 times
8E9640 00000000 00000000 01000091 00000000 [................]
8E9650 00000000 00000000 00000000 00000000 [................]
Repeat 185 times
8EA1F0 00000000 00000000 00000000 C9922303 [.............#..]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01000098 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01000098 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x01000090
Level 1 BMB for Low HWM block: 0x01000090
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01000091
Last Level 1 BMB: 0x01000090
Last Level II BMB: 0x01000091
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 17571 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01000090 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000090 Data dba: 0x01000093
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000091
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777363
Block dump from disk:
buffer tsn: 4 rdba: 0x01000093 (4/147)
scn: 0x0000.0096c992 seq: 0x02 flg: 0x04 tail: 0xc9920602
frmt: 0x02 chkval: 0xcd6c type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x008E8200 to 0x008EA200
8E8200 0000A206 01000093 0096C992 04020000 [................]
8E8210 0000CD6C 00000001 000044A3 0096C992 [l........D......]
8E8220 00000000 00320002 01000090 00000000 [......2.........]
8E8230 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
8E8260 00000000 00000000 000EFFFF 1F8A1F98 [................]
8E8270 00001F8A 00000000 00000000 00000000 [................]
8E8280 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
8EA1F0 00000000 00000000 00000000 C9920602 [................]
Block header dump: 0x01000093
Object id on Block? Y
seg/obj: 0x44a3 csc: 0x00.96c992 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000090 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
--11G默认用快速提交，Flag是U，正常提交是C。
--Itl: ITL事务槽号的流水编号
--Xid：transac[X]tion identified(事务ID),由und的段号+undo的槽号+undo槽号的覆盖次数三部分组成
--Uba：undo block address记录了最近一次的该记录的前镜像（修改前的值）
--Flag：C是提交，U是快速提交，---是未提交（Flg C=Committed U=Commit Upper Bound T=Active at CSC）
--Lck：锁住了几行数据，对应有几个行锁
--Scn/Fsc：Scn=SCN of commited TX; Fsc=Free space credit(bytes)
--SCN WRAP:如果事务已提交并完成清洗，该字段保存事务提交SCN的SCN WRAP部分，否则该字段保存空闲预支字节数(FSC).比如删除了一行数据10个字节，在事务提前前，这10个字节就属于fsc（即会写到SCN WRAP），只有事务提交后，才能正式返回到空闲空间。
bdba: 0x01000093
data_block_dump,data header at 0x8e8264
===============
tsiz: 0x1f98
hsiz: 0xe
pbl: 0x008e8264
76543210
flag=--------
ntab=0
nrow=0
frre=-1
fsbo=0xe
fseo=0x1f98
avsp=0x1f8a
tosp=0x1f8a
block_row_dump:
end_of_block_dump
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777364
Block dump from disk:
buffer tsn: 4 rdba: 0x01000094 (4/148)
scn: 0x0000.0096c992 seq: 0x02 flg: 0x04 tail: 0xc9920602
frmt: 0x02 chkval: 0xcd6b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x008E8200 to 0x008EA200
8E8200 0000A206 01000094 0096C992 04020000 [................]
8E8210 0000CD6B 00000001 000044A3 0096C992 [k........D......]
8E8220 00000000 00320002 01000090 00000000 [......2.........]
8E8230 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
8E8260 00000000 00000000 000EFFFF 1F8A1F98 [................]
8E8270 00001F8A 00000000 00000000 00000000 [................]
8E8280 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
8EA1F0 00000000 00000000 00000000 C9920602 [................]
Block header dump: 0x01000094
Object id on Block? Y
seg/obj: 0x44a3 csc: 0x00.96c992 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000090 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01000094
data_block_dump,data header at 0x8e8264
===============
tsiz: 0x1f98
hsiz: 0xe
pbl: 0x008e8264
76543210
flag=--------
ntab=0
nrow=0
frre=-1
fsbo=0xe
fseo=0x1f98
avsp=0x1f8a
tosp=0x1f8a
block_row_dump:
end_of_block_dump
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777365
Block dump from disk:
buffer tsn: 4 rdba: 0x01000095 (4/149)
scn: 0x0000.009722f6 seq: 0x01 flg: 0x04 tail: 0x22f60601
frmt: 0x02 chkval: 0x52a5 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x008E8200 to 0x008EA200
8E8200 0000A206 01000095 009722F6 04010000 [........."......]
8E8210 000052A5 00000001 000044A3 009722F6 [.R.......D..."..]
8E8220 00000000 00320002 01000090 001F001B [......2.........]
8E8230 0000007A 01C01C55 00230121 0000A000 [z...U...!.#.....]
8E8240 00971D50 00000000 00000000 00000000 [P...............]
8E8250 00000000 00000000 00000000 00000000 [................]
8E8260 00000000 000E0100 002EFFFF 1D331D61 [............a.3.]
8E8270 00001D33 1F72000E 1F1C1F47 1EC61EF3 [3.....r.G.......]
8E8280 1E741E9D 1E261E4C 1DD51DFB 1D881DAF [..t.L.&.........]
8E8290 00001D61 00000000 00000000 00000000 [a...............]
8E82A0 00000000 00000000 00000000 00000000 [................]
Repeat 465 times
8E9FC0 00000000 08002C00 2350C203 4C494D06 [.....,....P#.MIL]
8E9FD0 0552454C 52454C43 4EC2034B B6770753 [LER.CLERK..NS.w.]
8E9FE0 01011701 0EC20201 0BC102FF 0308002C [............,...]
8E9FF0 040350C2 44524F46 414E4107 5453594C [.P..FORD.ANALYST]
8EA000 434CC203 0CB57707 01010103 FF1FC202 [..LC.w..........]
8EA010 2C15C102 C2020800 414A0550 0553454D [...,....P.JAMES.]
8EA020 52454C43 4DC2034B B5770763 0101030C [CLERK..Mc.w.....]
8EA030 0AC20301 C102FF33 08002C1F 4D4FC203 [....3....,....OM]
8EA040 41444105 4305534D 4B52454C 594EC203 [.ADAMS.CLERK..NY]
8EA050 05BB7707 01010117 FF0CC202 2C15C102 [.w.............,]
8EA060 C2030800 54062D4F 454E5255 41530852 [....O-.TURNER.SA]
8EA070 4D53454C C2034E41 7707634D 010809B5 [LESMAN..Mc.w....]
8EA080 C2020101 02800110 002C1FC1 4FC20308 [..........,....O]
8EA090 494B0428 5009474E 49534552 544E4544 [(.KING.PRESIDENT]
8EA0A0 B57707FF 0101110B 33C20201 0BC102FF [..w........3....]
8EA0B0 0308002C 05594EC2 544F4353 4E410754 [,....NY.SCOTT.AN]
8EA0C0 53594C41 4CC20354 BB770743 01011304 [ALYST..LC.w.....]
8EA0D0 1FC20201 15C102FF 0308002C 05534EC2 [........,....NS.]
8EA0E0 52414C43 414D074B 4547414E 4FC20352 [CLARK.MANAGER..O]
8EA0F0 B5770728 01010906 19C20301 C102FF33 [(.w.........3...]
8EA100 08002C0B 634DC203 414C4205 4D07454B [.,....Mc.BLAKE.M]
8EA110 47414E41 C2035245 7707284F 010105B5 [ANAGER..O(.w....]
8EA120 C2030101 02FF331D 002C1FC1 4DC20308 [.....3....,....M]
8EA130 414D0637 4E495452 4C415308 414D5345 [7.MARTIN.SALESMA]
8EA140 4DC2034E B5770763 01011C09 0DC20301 [N..Mc.w.........]
8EA150 0FC20233 2C1FC102 C2030800 4A05434C [3......,....LC.J]
8EA160 53454E4F 4E414D07 52454741 284FC203 [ONES.MANAGER..O(]
8EA170 04B57707 01010102 4C1EC203 15C102FF [.w.........L....]
8EA180 0308002C 04164CC2 44524157 4C415308 [,....L..WARD.SAL]
8EA190 414D5345 4DC2034E B5770763 01011602 [ESMAN..Mc.w.....]
8EA1A0 0DC20301 06C20233 2C1FC102 C2030800 [....3......,....]
8EA1B0 4105644B 4E454C4C 4C415308 414D5345 [Kd.ALLEN.SALESMA]
8EA1C0 4DC2034E B5770763 01011402 11C20201 [N..Mc.w.........]
8EA1D0 0204C202 002C1FC1 4AC20308 4D530546 [......,....JF.SM]
8EA1E0 05485449 52454C43 50C2034B B4770703 [ITH.CLERK..P..w.]
8EA1F0 0101110C 09C20201 15C102FF 22F60601 [..............."]
Block header dump: 0x01000095
Object id on Block? Y
seg/obj: 0x44a3 csc: 0x00.9722f6 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000090 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x001b.01f.0000007a 0x01c01c55.0121.23 C-U- 0 scn 0x0000.00971d50
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01000095
data_block_dump,data header at 0x8e8264
===============
tsiz: 0x1f98
hsiz: 0x2e
pbl: 0x008e8264
76543210
flag=--------
ntab=1
nrow=14
frre=-1
fsbo=0x2e
fseo=0x1d61
avsp=0x1d33
tosp=0x1d33
0xe:pti[0] nrow=14 offs=0
0x12:pri[0] offs=0x1f72
0x14:pri[1] offs=0x1f47
0x16:pri[2] offs=0x1f1c
0x18:pri[3] offs=0x1ef3
0x1a:pri[4] offs=0x1ec6
0x1c:pri[5] offs=0x1e9d
0x1e:pri[6] offs=0x1e74
0x20:pri[7] offs=0x1e4c
0x22:pri[8] offs=0x1e26
0x24:pri[9] offs=0x1dfb
0x26:pri[10] offs=0x1dd5
0x28:pri[11] offs=0x1daf
0x2a:pri[12] offs=0x1d88
0x2c:pri[13] offs=0x1d61
block_row_dump:
tab 0, row 0, @0x1f72
tl: 38 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4a 46
col 1: [ 5] 53 4d 49 54 48
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 50 03
col 4: [ 7] 77 b4 0c 11 01 01 01
col 5: [ 2] c2 09
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 1, @0x1f47
tl: 43 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4b 64
col 1: [ 5] 41 4c 4c 45 4e
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 02 14 01 01 01
col 5: [ 2] c2 11
col 6: [ 2] c2 04
col 7: [ 2] c1 1f
tab 0, row 2, @0x1f1c
tl: 43 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4c 16
col 1: [ 4] 57 41 52 44
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 02 16 01 01 01
col 5: [ 3] c2 0d 33
col 6: [ 2] c2 06
col 7: [ 2] c1 1f
tab 0, row 3, @0x1ef3
tl: 41 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4c 43
col 1: [ 5] 4a 4f 4e 45 53
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 04 02 01 01 01
col 5: [ 3] c2 1e 4c
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 4, @0x1ec6
tl: 45 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4d 37
col 1: [ 6] 4d 41 52 54 49 4e
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 09 1c 01 01 01
col 5: [ 3] c2 0d 33
col 6: [ 2] c2 0f
col 7: [ 2] c1 1f
tab 0, row 5, @0x1e9d
tl: 41 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4d 63
col 1: [ 5] 42 4c 41 4b 45
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 05 01 01 01 01
col 5: [ 3] c2 1d 33
col 6: *NULL*
col 7: [ 2] c1 1f
tab 0, row 6, @0x1e74
tl: 41 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4e 53
col 1: [ 5] 43 4c 41 52 4b
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 06 09 01 01 01
col 5: [ 3] c2 19 33
col 6: *NULL*
col 7: [ 2] c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4e 59
col 1: [ 5] 53 43 4f 54 54
col 2: [ 7] 41 4e 41 4c 59 53 54
col 3: [ 3] c2 4c 43
col 4: [ 7] 77 bb 04 13 01 01 01
col 5: [ 2] c2 1f
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 8, @0x1e26
tl: 38 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4f 28
col 1: [ 4] 4b 49 4e 47
col 2: [ 9] 50 52 45 53 49 44 45 4e 54
col 3: *NULL*
col 4: [ 7] 77 b5 0b 11 01 01 01
col 5: [ 2] c2 33
col 6: *NULL*
col 7: [ 2] c1 0b
tab 0, row 9, @0x1dfb
tl: 43 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4f 2d
col 1: [ 6] 54 55 52 4e 45 52
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 09 08 01 01 01
col 5: [ 2] c2 10
col 6: [ 1] 80
col 7: [ 2] c1 1f
tab 0, row 10, @0x1dd5
tl: 38 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4f 4d
col 1: [ 5] 41 44 41 4d 53
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 4e 59
col 4: [ 7] 77 bb 05 17 01 01 01
col 5: [ 2] c2 0c
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 11, @0x1daf
tl: 38 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 2] c2 50
col 1: [ 5] 4a 41 4d 45 53
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 0c 03 01 01 01
col 5: [ 3] c2 0a 33
col 6: *NULL*
col 7: [ 2] c1 1f
tab 0, row 12, @0x1d88
tl: 39 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 50 03
col 1: [ 4] 46 4f 52 44
col 2: [ 7] 41 4e 41 4c 59 53 54
col 3: [ 3] c2 4c 43
col 4: [ 7] 77 b5 0c 03 01 01 01
col 5: [ 2] c2 1f
col 6: *NULL*
col 7: [ 2] c1 15
tab 0, row 13, @0x1d61
tl: 39 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 50 23
col 1: [ 6] 4d 49 4c 4c 45 52
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 4e 53
col 4: [ 7] 77 b6 01 17 01 01 01
col 5: [ 2] c2 0e
col 6: *NULL*
col 7: [ 2] c1 0b
end_of_block_dump
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777366
Block dump from disk:
buffer tsn: 4 rdba: 0x01000096 (4/150)
scn: 0x0000.0096c992 seq: 0x02 flg: 0x04 tail: 0xc9920602
frmt: 0x02 chkval: 0xcd69 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x008E8200 to 0x008EA200
8E8200 0000A206 01000096 0096C992 04020000 [................]
8E8210 0000CD69 00000001 000044A3 0096C992 [i........D......]
8E8220 00000000 00320002 01000090 00000000 [......2.........]
8E8230 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
8E8260 00000000 00000000 000EFFFF 1F8A1F98 [................]
8E8270 00001F8A 00000000 00000000 00000000 [................]
8E8280 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
8EA1F0 00000000 00000000 00000000 C9920602 [................]
Block header dump: 0x01000096
Object id on Block? Y
seg/obj: 0x44a3 csc: 0x00.96c992 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000090 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01000096
data_block_dump,data header at 0x8e8264
===============
tsiz: 0x1f98
hsiz: 0xe
pbl: 0x008e8264
76543210
flag=--------
ntab=0
nrow=0
frre=-1
fsbo=0xe
fseo=0x1f98
avsp=0x1f8a
tosp=0x1f8a
block_row_dump:
end_of_block_dump
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16777367
Block dump from disk:
buffer tsn: 4 rdba: 0x01000097 (4/151)
scn: 0x0000.0096c992 seq: 0x02 flg: 0x04 tail: 0xc9920602
frmt: 0x02 chkval: 0xcd68 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x008E8200 to 0x008EA200
8E8200 0000A206 01000097 0096C992 04020000 [................]
8E8210 0000CD68 00000001 000044A3 0096C992 [h........D......]
8E8220 00000000 00320002 01000090 00000000 [......2.........]
8E8230 00000000 00000000 00000000 00000000 [................]
Repeat 2 times
8E8260 00000000 00000000 000EFFFF 1F8A1F98 [................]
8E8270 00001F8A 00000000 00000000 00000000 [................]
8E8280 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
8EA1F0 00000000 00000000 00000000 C9920602 [................]
Block header dump: 0x01000097
Object id on Block? Y
seg/obj: 0x44a3 csc: 0x00.96c992 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000090 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x01000097
data_block_dump,data header at 0x8e8264
===============
tsiz: 0x1f98
hsiz: 0xe
pbl: 0x008e8264
76543210
flag=--------
ntab=0
nrow=0
frre=-1
fsbo=0xe
fseo=0x1f98
avsp=0x1f8a
tosp=0x1f8a
block_row_dump:
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 144 maxblk 151查看emp表存储记录的block:
11:14:36 SYS@ test1 >conn scott/tiger
Connected.
11:14:39 SCOTT@ test1 >select rowid,ename from emp where rownum=1;
ROWID ENAME
------------------ ----------
AAAESjAAEAAAACVAAA SMITH
11:15:05 SCOTT@ test1 >select dbms_rowid.ROWID_RELATIVE_FNO(rowid) ,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) from emp where rownum=1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 149从以上dump可以看到，第149#块开始用于存储数据（144-148用于segment header）
