从Oracle 11gR2开始,每个数据文件的前128个块被Oracle留用,其中0-1号块为文件头,2-127号块是位图块,记录表空间中区的分配情况。
test表空间是一个新建的表空间,里面有一张表ms1
SQL> SELECT * FROM MS1;
A
----------
1
SQL> SELECT EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS FROM DBA_EXTENTS WHERE SEGMENT_NAME='MS1' order by extent_id;
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 18 128 8
可以看到ms1这张表的第一个块是从128号分配的,一次性分配了8个块。一个块是8k,那么8个块是64k,正好是表定义中的initial值65536。
dump出文件头
SQL> alter system set events 'immediate trace name file_hdrs level 3';
System altered.
只关注18号数据文件,也就是test表空间的数据文件
DATA FILE #18:
name #32: /opt/mingdbdata/data/MINGPDB1/tbs_test01.dbf
creation size=12800 block size=8192 status=0xe flg=0x1 head=32 tail=32 dup=1
pdb_id 4, tablespace 9, index=17 krfil=18 prev_file_in_ts=0 prev_file_in_pdb=16
unrecoverable scn: 0x0000000000000000 01/01/1988 00:00:00
Checkpoint cnt:4060 scn: 0x0000000003d8c8df 01/15/2019 10:40:27
Stop scn: 0xffffffffffffffff 11/27/2018 17:14:13
Creation Checkpointed at scn: 0x0000000002547e5c 10/23/2018 17:22:12
thread:1 rba:(0xfeb.7d7.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
Offline scn: 0x0000000002eda638 prev_range: 161
Online Checkpointed at scn: 0x0000000002edab34 11/27/2018 17:20:46
thread:1 rba:(0x1687.8a.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
Hot Backup end marker scn: 0x0000000000000000
aux_file is NOT DEFINED
Plugged readony: NO
Plugin scnscn: 0x0000000000000000
Plugin resetlogs scn/timescn: 0x0000000000000000 01/01/1988 00:00:00
Foreign creation scn/timescn: 0x0000000000000000 01/01/1988 00:00:00
Foreign checkpoint scn/timescn: 0x0000000000000000 01/01/1988 00:00:00
Online move state: 0
V10 STYLE FILE HEADER:
Compatibility Vsn = 203423744=0xc200000
Db ID=2547745710=0x97db83ae, Db Name='MINGDB'
Activation ID=0=0x0
Control Seq=986330575=0x3aca35cf, File size=12800=0x3200
File Number=18, Blksiz=8192, File Type=3 DATA
Tablespace #9 - TEST rel_fn:18
Creation at scn: 0x0000000002547e5c 10/23/2018 17:22:12
Backup taken at scn: 0x0000000000000000 01/01/1988 00:00:00 thread:0
reset logs count:0x3a7ae32e scn: 0x0000000000000001
prev reset logs count:0x0 scn: 0x0000000000000000
recovered at 11/19/2018 17:53:03
status:0x4 root dba:0x00400208 chkpt cnt: 4060 ctl cnt:4059
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000000003d8c8df 01/15/2019 10:40:27
thread:1 rba:(0x1fae.2.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
Backup Checkpointed at scn: 0x0000000000000000
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
External cache id: 0x0 0x0 0x0 0x0
Absolute fuzzy scn: 0x0000000000000000
Recovery fuzzy scn: 0x0000000000000000 01/01/1988 00:00:00
Terminal Recovery Stamp 01/01/1988 00:00:00
PDB ID=4, PDB Db ID=979682397=0x3a64c45d, PDB UID=979682397=0x3a64c45d
Previous recovery fuzzy scn: 0x0000000000000000
Last deallocation scn: 0x0000000000000000
Plugged-in scn: 0x0000000000000000
Plugin resetlogs scn: 0x0000000000000000
Foreign creation scn: 0x0000000000000000
Foreign checkpoint scn: 0x0000000000000000
EOF section checkpoint scn: 0x0000000000000000
Undo optimization current scn: 0x0000000000000000
File key structure: ena 0 flg 0x0 mkloc 0
key: 0000000000000000000000000000000000000000000000000000000000000000
mkeyid: 00000000000000000000000000000000
Last read CF transaction OCX clock 0
Platform Information: Creation Platform ID: 13
Current Platform ID: 13 Last Platform ID: 13
PDB incarnation 0: inc_scn 0x0000000000000000 inc_time 0, br_scn 0x0000000000000000 br_time 0, er_scn 0x0000000000000000 er_time 0
利用如下语句,可以从块的dump文件中得到块的类型:
alter system dump datafile 18 block 128;
2号块:Bitmapped File Space Header
3-127号块Bitmapped File Space Bitmap
128号块:FIRST LEVEL BITMAP BLOCK
SQL> SELECT EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS FROM DBA_EXTENTS WHERE SEGMENT_NAME='MS1' order by extent_id;
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 18 128 8
128号块是L1,那么129号块是L2,130号块是L3,将这三个块dump出来
SQL> alter system dump datafile 18 block min 128 block max 130;
System altered.
从trace文件中可以看到L3块中
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x04800081
Last Level 1 BMB: 0x04800080
Last Level II BMB: 0x04800081
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 53759 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x04800080 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x04800080 Data dba: 0x04800083
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x04800081
nl2:1代表L2块的个数是1.
Second Level Bitmap block DBAs的DBA是0x04800081
SQL> select dbms_utility.data_block_address_file(TO_NUMBER('04800081', 'XXXXXXXX')) file# ,dbms_utility.data_block_address_block(TO_NUMBER('04800081', 'XXXXXXXX')) block# from dual;
FILE# BLOCK#
---------- ----------
18 129
正是129号块。
L2 Hint for inserts: 0x04800081
04800081是129号块。
L2 Hint for inserts代表着插入的时候选择会选择这个L2块,从L2块中寻找空闲空间,根据L2块选择L1块的时候就是随机的了。会根据执行insert的session的pid号进行hash运算,根据hash value分配到相应的L1块中。
Last Level 1 BMB: 0x04800080
SQL> select dbms_utility.data_block_address_file(TO_NUMBER('4800080', 'XXXXXXXX')) file# ,dbms_utility.data_block_address_block(TO_NUMBER('4800080', 'XXXXXXXX')) block# from dual;
FILE# BLOCK#
---------- ----------
18 128
L1块的地址是128号块。
下面这部分描述了区的初始块地址和块的个数。该区128号开始,分配了8个块。
Extent Map
-----------------------------------------------------------------
0x04800080 length: 8
Auxillary Map描述了该区的L1块地址和存储实际数据的第一个块地址
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x04800080 Data dba: 0x04800083
--------------------------------------------------------
Data dba: 0x04800083数据块的地址是从131块开始的。
下面这一段trace体现了高水位信息:
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x04800088 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: 0x04800080
Level 1 BMB for Low HWM block: 0x04800080
--------------------------------------------------------
SQL> select dbms_utility.data_block_address_file(TO_NUMBER('04800088', 'XXXXXXXX')) file# ,dbms_utility.data_block_address_block(TO_NUMBER('04800088', 'XXXXXXXX')) block# from dual;
FILE# BLOCK#
---------- ----------
18 136
高水位是136号块,这个块下面有5个块,分别是131-135号,131正好是第一个数据块,135正好是初始分配该表的8个块中的最后一个块。
128 129 130 131 132 133 134 135 |136
L1 L2 L3 第一个数据块 |高水位线
Oracle在上调高水位线的时候,上调的范围是以L1中块的个数。
L2块中dump的内容下面部分跟L3差异不大
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x04800088 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:: 0x04800088 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: 0x04800080
Level 1 BMB for Low HWM block: 0x04800080
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x04800081
Last Level 1 BMB: 0x04800080
Last Level II BMB: 0x04800081
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 53759 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x04800080 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x04800080 Data dba: 0x04800083
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x04800081
这部分
Dump of Second Level Bitmap Block
number: 1 nfree: 1 ffree: 0 pdba: 0x04800082
Inc #: 0 Objd: 53759 Flag: 3
opcode:0
xid:
L1 Ranges :
--------------------------------------------------------
0x04800080 Free: 7 Inst: 1
--------------------------------------------------------
number:1 该L2块中有1个L1块
nfree: 1 该L2块管理的L1块中,有一个L1块下有空闲空间
ffree: 0 该L2块管理的L1块中,有零个L1块下没有空闲空间
pdba:0x04800082 即parent dba,代表了L3的地址。
L1的地址是0x04800080,即128号块
L1块部分dump文件内容:
Dump of First Level Bitmap Block
--------------------------------
nbits : 4 nranges: 1 parent dba: 0x04800081 poffset: 0
unformatted: 0 total: 8 first useful block: 3
owning instance : 1
instance ownership changed at 01/07/2019 09:44:36
Last successful Search 01/07/2019 09:44:36
Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0 nf5 5
Extent Map Block Offset: 4294967295
First free datablock : 3
Bitmap block lock opcode 0
Locker xid: : 0x0000.000.00000000
Dealloc scn(ub4/ub4): (0x00000000.02d0e7a4)
Format scn: 0x0000000003b52203
Flag: 0x000002a1 (REJCTX/-/AUX/-/OBJD/-/-/-)
Inc #: 0 Objd: 53759
HWM Flag: HWM Set
Highwater:: 0x04800088 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x04800080 Length: 8 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:100% free
4:100% free 5:100% free 6:100% free 7:100% free
--------------------------------------------------------
nbits : 4 位图空间管理用数据块头中4bit来管理块的可用空间。
nranges: 1 该L1管理了1个区
parent dba: 0x04800081 即L2块的地址
unformatted:0 高水位线与低水位线之间未格式化的块的数量
total: 8 L1块管理的块的数量
first useful block: 3 第一个可用于存储实际数据的块,从0开始计算。这里是第四个,因为0,1,2是L1,L2,L3块
DBA Ranges 这部分描述了128-135号块的使用情况和属性。Metadata代表L1,L2,L3块。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31480688/viewspace-2636533/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31480688/viewspace-2636533/