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用于记录区间的位图信息。
11G:0号操作系统块，1-2是文件头，3-127是位图信息。128号开始及之后存放的是数据了—可能是段头或段的数据。
案例：
t2表是test2表空间的第一个segment：
11:24:30 SYS@ test1 >col owner for a1011: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 811: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 127Block 1 (file header) not dumped:use dump file header commandBlock dump from cache:Dump of buffer cache at level 4 for tsn=22, rdba=12582914BH (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: 0Block dump from disk:buffer tsn: 22 rdba: 0x00c00002 (3/2)scn: 0x0000.0096c8a5 seq: 0x02 flg: 0x04 tail: 0xc8a51d02frmt: 0x02 chkval: 0x72e8 type: 0x1d=KTFB Bitmapped File Space HeaderHex dump of block: st=0, typ_found=1Dump of memory from 0x00758200 to 0x0075A200758200 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 times75A1F0 00000000 00000000 00000000 C8A51D02 [................]File Space Header Block:Header Control:RelFno: 3, Unit: 8, Size: 1280, Flag: 1AutoExtend: NO, Increment: 0, MaxSize: 0Initial Area: 126, Tail: 1279, First: 0, Free: 144Deallocation scn: 9881764.0Header Opcode:Save: No Pending Op
block# 127:
Block dump from cache:Dump of buffer cache at level 4 for tsn=22, rdba=12583039Block dump from disk:buffer tsn: 22 rdba: 0x00c0007f (3/127)scn: 0x0000.0090e601 seq: 0x01 flg: 0x04 tail: 0xe6011e01frmt: 0x02 chkval: 0x4373 type: 0x1e=KTFB Bitmapped File Space BitmapHex dump of block: st=0, typ_found=1Dump of memory from 0x00758200 to 0x0075A200758200 0000A21E 00C0007F 0090E601 04010000 [................]758210 00004373 00000003 03C10080 00000000 [sC..............]758220 00000000 0000F800 00000000 00000000 [................]758230 00000000 00000000 00000000 00000000 [................] Repeat 507 times75A1F0 00000000 00000000 00000000 E6011E01 [................]File Space Bitmap Block:BitMap Control:RelFno: 3, BeginBlock: 62980224, Flag: 0, First: 0, Free: 634880000000000000000 0000000000000000 0000000000000000 00000000000000000000000000000000 0000000000000000 0000000000000000 00000000000000000000000000000000 0000000000000000 0000000000000000 00000000000000000000000000000000 0000000000000000 0000000000000000 0000000000000000
案例分析：
1、查看emp表存储信息
17:31:39 SYS@ test1 >col segment_name for a2017:33:01 SYS@ test1 >select owner,segment_name,segment_type,tablespace_name,bytes/1024 from dba_segments17:33:24 2 where segment_name='EMP';OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024------------------------------ -------------------- ------------------ ------------------------------ ----------SCOTT EMP TABLE USERS 6417: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 MANUALUSERS PERMANENT LOCAL AUTO17:37:42 SYS@ test1 >COL OWNER FOR A1017: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 817: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.trcOracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1System name: LinuxNode name: rh6.cuug.netRelease: 2.6.32-71.el6.i686Version: #1 SMP Wed Sep 1 01:26:34 EDT 2010Machine: i686Instance name: test1Redo thread mounted by this instance: 1Oracle process number: 0Unix process pid: 2385, p_w_picpath: oracle@rh6.cuug.net*** 2014-04-22 10:33:45.255Breaking the connection before proto/dty negotiation, error raised 3113Trace file /u01/app/oracle/diag/rdbms/test1/test1/trace/test1_ora_2385.trcOracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1System name: LinuxNode name: RH6.cuug.netRelease: 2.6.32-71.el6.i686Version: #1 SMP Wed Sep 1 01:26:34 EDT 2010Machine: i686Instance name: test1Redo thread mounted by this instance: 1Oracle process number: 17Unix 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.781Thread 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.995Started writing zeroblks thread 1 seq 41 blocks 7085-7092*** 2014-12-15 17:35:48.030Completed 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/secLongest record: 1Kb, moves: 0/41 (0%)Change moves: 1/11 (9%), moved: 0MbLongest LWN: 9Kb, moves: 0/3 (0%), moved: 0MbLast redo scn: 0x0000.009a83ac (10126252)--------------------------------------------------- Recovery Hash Table Statistics ---------Hash table buckets = 32768Longest hash chain = 1Average hash chain = 25/25 = 1.0Max compares per lookup = 1Avg compares per lookup = 30/67 = 0.4----------------------------------------------*** 2014-12-15 17:35:48.085KCRA: start recovery claims for 25 data blocks*** 2014-12-15 17:35:48.256KCRA: blocks processed = 25/25, claimed = 25, eliminated = 0*** 2014-12-15 17:35:48.257Recovery of Online Redo Log: Thread 1 Group 4 Seq 41 Reading mem 0*** 2014-12-15 17:35:48.382Completed redo application of 0.01MB*** 2014-12-15 17:35:50.522Completed recovery checkpointIR RIA: redo_size 12288 bytes, time_taken 267 ms----- Recovery Hash Table Statistics ---------Hash table buckets = 32768Longest hash chain = 1Average hash chain = 25/25 = 1.0Max compares per lookup = 1Avg 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.120kwqmnich: current time:: 9: 36: 8: 0kwqmnich: instance no 0 repartition flag 1kwqmnich: initialized job cache structurekwqinfy: Call kwqrNondurSubInstTsk*** 2014-12-15 17:42:08.722Start dump data blocks tsn: 4 file#:4 minblk 144 maxblk 151Block dump from cache:Dump of buffer cache at level 4 for tsn=4, rdba=16777360
Block 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=1Dump of memory from 0x008E8200 to 0x008EA2008E8200 0000A220 01000090 0096C992 04020000 [ ...............]8E8210 00004BC8 00000000 00000000 00000000 [.K..............]8E8220 00000000 00000000 00000000 00000000 [................] Repeat 1 times8E8240 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 times8E8380 00000000 00000000 00000000 55551511 [..............UU]8E8390 00000000 00000000 00000000 00000000 [................] Repeat 485 times8EA1F0 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=16777361
Block 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=1Dump of memory from 0x008E8200 to 0x008EA2008E8200 0000A221 01000091 0096C97A 04020000 [!.......z.......]8E8210 0000C282 00000000 00000000 00000000 [................]8E8220 00000000 00000000 00000000 00000000 [................] Repeat 1 times8E8240 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 times8EA1F0 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=16777362BH (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: 0BH (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: 0
Block 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=1Dump of memory from 0x008E8200 to 0x008EA2008E8200 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 times8E82D0 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 times8E8CB0 01000090 01000093 00000000 00000000 [................]8E8CC0 00000000 00000000 00000000 00000000 [................] Repeat 151 times8E9640 00000000 00000000 01000091 00000000 [................]8E9650 00000000 00000000 00000000 00000000 [................] Repeat 185 times8EA1F0 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=16777363Block dump from disk:buffer tsn: 4 rdba: 0x01000093 (4/147)scn: 0x0000.0096c992 seq: 0x02 flg: 0x04 tail: 0xc9920602frmt: 0x02 chkval: 0xcd6c type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x008E8200 to 0x008EA2008E8200 0000A206 01000093 0096C992 04020000 [................]8E8210 0000CD6C 00000001 000044A3 0096C992 [l........D......]8E8220 00000000 00320002 01000090 00000000 [......2.........]8E8230 00000000 00000000 00000000 00000000 [................] Repeat 2 times8E8260 00000000 00000000 000EFFFF 1F8A1F98 [................]8E8270 00001F8A 00000000 00000000 00000000 [................]8E8280 00000000 00000000 00000000 00000000 [................] Repeat 502 times8EA1F0 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/Fsc0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.000000000x02 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: 0x01000093data_block_dump,data header at 0x8e8264===============tsiz: 0x1f98hsiz: 0xepbl: 0x008e8264 76543210flag=--------ntab=0nrow=0frre=-1fsbo=0xefseo=0x1f98avsp=0x1f8atosp=0x1f8ablock_row_dump:end_of_block_dumpBlock dump from cache:Dump of buffer cache at level 4 for tsn=4, rdba=16777364Block dump from disk:buffer tsn: 4 rdba: 0x01000094 (4/148)scn: 0x0000.0096c992 seq: 0x02 flg: 0x04 tail: 0xc9920602frmt: 0x02 chkval: 0xcd6b type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x008E8200 to 0x008EA2008E8200 0000A206 01000094 0096C992 04020000 [................]8E8210 0000CD6B 00000001 000044A3 0096C992 [k........D......]8E8220 00000000 00320002 01000090 00000000 [......2.........]8E8230 00000000 00000000 00000000 00000000 [................] Repeat 2 times8E8260 00000000 00000000 000EFFFF 1F8A1F98 [................]8E8270 00001F8A 00000000 00000000 00000000 [................]8E8280 00000000 00000000 00000000 00000000 [................] Repeat 502 times8EA1F0 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/Fsc0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.000000000x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000bdba: 0x01000094data_block_dump,data header at 0x8e8264===============tsiz: 0x1f98hsiz: 0xepbl: 0x008e8264 76543210flag=--------ntab=0nrow=0frre=-1fsbo=0xefseo=0x1f98avsp=0x1f8atosp=0x1f8ablock_row_dump:end_of_block_dumpBlock dump from cache:Dump of buffer cache at level 4 for tsn=4, rdba=16777365Block dump from disk:buffer tsn: 4 rdba: 0x01000095 (4/149)scn: 0x0000.009722f6 seq: 0x01 flg: 0x04 tail: 0x22f60601frmt: 0x02 chkval: 0x52a5 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x008E8200 to 0x008EA2008E8200 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 times8E9FC0 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/Fsc0x01 0x001b.01f.0000007a 0x01c01c55.0121.23 C-U- 0 scn 0x0000.00971d500x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000bdba: 0x01000095data_block_dump,data header at 0x8e8264===============tsiz: 0x1f98hsiz: 0x2epbl: 0x008e8264 76543210flag=--------ntab=1nrow=14frre=-1fsbo=0x2efseo=0x1d61avsp=0x1d33tosp=0x1d330xe:pti[0] nrow=14 offs=00x12:pri[0] offs=0x1f720x14:pri[1] offs=0x1f470x16:pri[2] offs=0x1f1c0x18:pri[3] offs=0x1ef30x1a:pri[4] offs=0x1ec60x1c:pri[5] offs=0x1e9d0x1e:pri[6] offs=0x1e740x20:pri[7] offs=0x1e4c0x22:pri[8] offs=0x1e260x24:pri[9] offs=0x1dfb0x26:pri[10] offs=0x1dd50x28:pri[11] offs=0x1daf0x2a:pri[12] offs=0x1d880x2c:pri[13] offs=0x1d61block_row_dump:tab 0, row 0, @0x1f72tl: 38 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 4a 46col 1: [ 5] 53 4d 49 54 48col 2: [ 5] 43 4c 45 52 4bcol 3: [ 3] c2 50 03col 4: [ 7] 77 b4 0c 11 01 01 01col 5: [ 2] c2 09col 6: *NULL*col 7: [ 2] c1 15tab 0, row 1, @0x1f47tl: 43 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 4b 64col 1: [ 5] 41 4c 4c 45 4ecol 2: [ 8] 53 41 4c 45 53 4d 41 4ecol 3: [ 3] c2 4d 63col 4: [ 7] 77 b5 02 14 01 01 01col 5: [ 2] c2 11col 6: [ 2] c2 04col 7: [ 2] c1 1ftab 0, row 2, @0x1f1ctl: 43 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 4c 16col 1: [ 4] 57 41 52 44col 2: [ 8] 53 41 4c 45 53 4d 41 4ecol 3: [ 3] c2 4d 63col 4: [ 7] 77 b5 02 16 01 01 01col 5: [ 3] c2 0d 33col 6: [ 2] c2 06col 7: [ 2] c1 1ftab 0, row 3, @0x1ef3tl: 41 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 4c 43col 1: [ 5] 4a 4f 4e 45 53col 2: [ 7] 4d 41 4e 41 47 45 52col 3: [ 3] c2 4f 28col 4: [ 7] 77 b5 04 02 01 01 01col 5: [ 3] c2 1e 4ccol 6: *NULL*col 7: [ 2] c1 15tab 0, row 4, @0x1ec6tl: 45 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 4d 37col 1: [ 6] 4d 41 52 54 49 4ecol 2: [ 8] 53 41 4c 45 53 4d 41 4ecol 3: [ 3] c2 4d 63col 4: [ 7] 77 b5 09 1c 01 01 01col 5: [ 3] c2 0d 33col 6: [ 2] c2 0fcol 7: [ 2] c1 1ftab 0, row 5, @0x1e9dtl: 41 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 4d 63col 1: [ 5] 42 4c 41 4b 45col 2: [ 7] 4d 41 4e 41 47 45 52col 3: [ 3] c2 4f 28col 4: [ 7] 77 b5 05 01 01 01 01col 5: [ 3] c2 1d 33col 6: *NULL*col 7: [ 2] c1 1ftab 0, row 6, @0x1e74tl: 41 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 4e 53col 1: [ 5] 43 4c 41 52 4bcol 2: [ 7] 4d 41 4e 41 47 45 52col 3: [ 3] c2 4f 28col 4: [ 7] 77 b5 06 09 01 01 01col 5: [ 3] c2 19 33col 6: *NULL*col 7: [ 2] c1 0btab 0, row 7, @0x1e4ctl: 40 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 4e 59col 1: [ 5] 53 43 4f 54 54col 2: [ 7] 41 4e 41 4c 59 53 54col 3: [ 3] c2 4c 43col 4: [ 7] 77 bb 04 13 01 01 01col 5: [ 2] c2 1fcol 6: *NULL*col 7: [ 2] c1 15tab 0, row 8, @0x1e26tl: 38 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 4f 28col 1: [ 4] 4b 49 4e 47col 2: [ 9] 50 52 45 53 49 44 45 4e 54col 3: *NULL*col 4: [ 7] 77 b5 0b 11 01 01 01col 5: [ 2] c2 33col 6: *NULL*col 7: [ 2] c1 0btab 0, row 9, @0x1dfbtl: 43 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 4f 2dcol 1: [ 6] 54 55 52 4e 45 52col 2: [ 8] 53 41 4c 45 53 4d 41 4ecol 3: [ 3] c2 4d 63col 4: [ 7] 77 b5 09 08 01 01 01col 5: [ 2] c2 10col 6: [ 1] 80col 7: [ 2] c1 1ftab 0, row 10, @0x1dd5tl: 38 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 4f 4dcol 1: [ 5] 41 44 41 4d 53col 2: [ 5] 43 4c 45 52 4bcol 3: [ 3] c2 4e 59col 4: [ 7] 77 bb 05 17 01 01 01col 5: [ 2] c2 0ccol 6: *NULL*col 7: [ 2] c1 15tab 0, row 11, @0x1daftl: 38 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 2] c2 50col 1: [ 5] 4a 41 4d 45 53col 2: [ 5] 43 4c 45 52 4bcol 3: [ 3] c2 4d 63col 4: [ 7] 77 b5 0c 03 01 01 01col 5: [ 3] c2 0a 33col 6: *NULL*col 7: [ 2] c1 1ftab 0, row 12, @0x1d88tl: 39 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 50 03col 1: [ 4] 46 4f 52 44col 2: [ 7] 41 4e 41 4c 59 53 54col 3: [ 3] c2 4c 43col 4: [ 7] 77 b5 0c 03 01 01 01col 5: [ 2] c2 1fcol 6: *NULL*col 7: [ 2] c1 15tab 0, row 13, @0x1d61tl: 39 fb: --H-FL-- lb: 0x0 cc: 8col 0: [ 3] c2 50 23col 1: [ 6] 4d 49 4c 4c 45 52col 2: [ 5] 43 4c 45 52 4bcol 3: [ 3] c2 4e 53col 4: [ 7] 77 b6 01 17 01 01 01col 5: [ 2] c2 0ecol 6: *NULL*col 7: [ 2] c1 0bend_of_block_dumpBlock dump from cache:Dump of buffer cache at level 4 for tsn=4, rdba=16777366Block dump from disk:buffer tsn: 4 rdba: 0x01000096 (4/150)scn: 0x0000.0096c992 seq: 0x02 flg: 0x04 tail: 0xc9920602frmt: 0x02 chkval: 0xcd69 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x008E8200 to 0x008EA2008E8200 0000A206 01000096 0096C992 04020000 [................]8E8210 0000CD69 00000001 000044A3 0096C992 [i........D......]8E8220 00000000 00320002 01000090 00000000 [......2.........]8E8230 00000000 00000000 00000000 00000000 [................] Repeat 2 times8E8260 00000000 00000000 000EFFFF 1F8A1F98 [................]8E8270 00001F8A 00000000 00000000 00000000 [................]8E8280 00000000 00000000 00000000 00000000 [................] Repeat 502 times8EA1F0 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/Fsc0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.000000000x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000bdba: 0x01000096data_block_dump,data header at 0x8e8264===============tsiz: 0x1f98hsiz: 0xepbl: 0x008e8264 76543210flag=--------ntab=0nrow=0frre=-1fsbo=0xefseo=0x1f98avsp=0x1f8atosp=0x1f8ablock_row_dump:end_of_block_dumpBlock dump from cache:Dump of buffer cache at level 4 for tsn=4, rdba=16777367Block dump from disk:buffer tsn: 4 rdba: 0x01000097 (4/151)scn: 0x0000.0096c992 seq: 0x02 flg: 0x04 tail: 0xc9920602frmt: 0x02 chkval: 0xcd68 type: 0x06=trans dataHex dump of block: st=0, typ_found=1Dump of memory from 0x008E8200 to 0x008EA2008E8200 0000A206 01000097 0096C992 04020000 [................]8E8210 0000CD68 00000001 000044A3 0096C992 [h........D......]8E8220 00000000 00320002 01000090 00000000 [......2.........]8E8230 00000000 00000000 00000000 00000000 [................] Repeat 2 times8E8260 00000000 00000000 000EFFFF 1F8A1F98 [................]8E8270 00001F8A 00000000 00000000 00000000 [................]8E8280 00000000 00000000 00000000 00000000 [................] Repeat 502 times8EA1F0 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/Fsc0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.000000000x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000bdba: 0x01000097data_block_dump,data header at 0x8e8264===============tsiz: 0x1f98hsiz: 0xepbl: 0x008e8264 76543210flag=--------ntab=0nrow=0frre=-1fsbo=0xefseo=0x1f98avsp=0x1f8atosp=0x1f8ablock_row_dump:end_of_block_dumpEnd dump data blocks tsn: 4 file#: 4 minblk 144 maxblk 151
查看emp表存储记录的block:
11:14:36 SYS@ test1 >conn scott/tigerConnected.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）