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 Op
block# 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=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=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=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=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: 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=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）
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
