Oracle ASSM表空间存储结构

从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块。