|
创建测试表
SQL> create table test_block as select * from dba_objects where = ;
表已创建
SQL> select OWNER SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS FREELISTS from dba_segments where segment_name = TEST_BLOCK;
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS TEST_BLOCK
SQL> select EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO from dba_extents where segment_name = TEST_BLOCK;
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
SQL> insert into test_block select * from dba_objects where rownum< ;
已创建 行
SQL> select dbms_rowidrowid_relative_fno(rowid) file# dbms_rowidrowid_block_number(rowid) block# from test_block;
FILE# BLOCK#
已选择行
数据插入到了第块那么被称为 HEADER_BLOCK 的第块是做什么的呢？
SQL> alter system dump datafile block ;
系统已更改
SQL> select pvalue||\||iinstance_name||_ora_||pspid||trc trace_path from v$process p v$instance i v$parameter p where paddr = ( select paddr from v$session where sid = ( select sid from v$mystat where rownum = ) ) and pname=user_dump_dest;
TRACE_PATH
D:\oracle\admin\ora\udump\orasid_ora_trc
我们看看块中的信息
*** :: Start dump data blocks tsn: file#: minblk maxblk buffer tsn: rdba: xcc (/) scn: xbfaff seq: x flg: x tail: xaff frmt: x chkval: x type: x=DATA SEGMENT HEADER UNLIMITED Extent Control Header Extent Header:: spare: spare: #extents: #blocks: last map x #maps: offset: Highwater:: xcc ext#: blk#: ext size: #blocks in seg hdrs freelists: #blocks below: mapblk x offset: Unlocked Map Header:: next x #extents: obj#: flag: x Extent Map xcc length: nfl = nfb = typ = nxf = ccnt = SEG LST:: flg: USED lhd: xcc ltl: xcc End dump data blocks tsn: file#: minblk maxblk
从 type 中可以看到第一个块是数据段的头信息我们看一下这里的高水位标识（Highwater）
SQL> select to_number(c xxxxxxxxxx) Highwater from dual;
HIGHWATER
现在高水位是在块我再插入一条数据看看
SQL> insert into test_block select * from dba_objects where rownum = ;
已创建 行
SQL> select dbms_rowidrowid_block_number(rowid) block# from test_block;
BLOCK#
已选择行
SQL> rollback;
回退已完成
SQL> insert/*+ append */ into test_block select * from dba_objects where rownum = ;
已创建 行
SQL> select dbms_rowidrowid_block_number(rowid) block# from test_block; select dbms_rowidrowid_block_number(rowid) block# from test_block * ERROR 位于第 行: ORA: 无法在并行模式下修改之后读/修改对象
SQL> alter system dump datafile block ;
系统已更改
此时块信息如下
*** :: *** SESSION ID:() :: Start dump data blocks tsn: file#: minblk maxblk buffer tsn: rdba: xcc (/) scn: xbfaff seq: x flg: x tail: xaff frmt: x chkval: x type: x=DATA SEGMENT HEADER UNLIMITED Extent Control Header
Extent Header:: spare: spare: #extents: #blocks: last map x #maps: offset: Highwater:: xcc ext#: blk#: ext size: #blocks in seg hdrs freelists: #blocks below: mapblk x offset: Unlocked Map Header:: next x #extents: obj#: flag: x Extent Map
xcc length:
nfl = nfb = typ = nxf = ccnt = SEG LST:: flg: USED lhd: xcc ltl: xcc End dump data blocks tsn: file#: minblk maxblk
append 一条数据后在提交前高水位并没有发生变化但此时所插入的数据已经保存在第块了此时第块信息如下
*** :: Start dump data blocks tsn: file#: minblk maxblk buffer tsn: rdba: xcc (/) scn: xbfc seq: x flg: x tail: xc frmt: x chkval: x type: x=trans data Block header dump: xcc Object id on Block? Y seg/obj: xcfd csc: xbfc itc: flg: typ: DATA fsl: fnx: x ver: x
Itl Xid Uba Flag Lck Scn/Fsc x x x fsc x x x x fsc x x x x fsc x
data_block_dumpdata header at x =============== tsiz: xf hsiz: x pbl: x bdba: xcc
flag= ntab= nrow= frre= fsbo=x fseo=xf avsp=xf tosp=xf xe:pti[] nrow= offs= x:pri[] offs=xf block_row_dump: tab row @xf tl: fb: HFL lb: x cc: col : [ ] col : [] f f c e b f e e col : *NULL* col : [ ] c col : *NULL* col : [] a c col : [ ] b f d col : [ ] b f d col : [] d d a a a col : [ ] c col : [ ] e col : [ ] e col : [ ] e end_of_block_dump End dump data blocks tsn: file#: minblk maxblk
我们继续刚才的操作提交数据看此时数据在数据块中的分布
SQL> commit;
提交完成
SQL> select dbms_rowidrowid_block_number(rowid) block# from test_block;
BLOCK#
已选择行 看看此时的高水位
SQL> alter system dump datafile block ;
系统已更改
块的内容如下
*** :: Start dump data blocks tsn: file#: minblk maxblk buffer tsn: rdba: xcc (/) scn: xbfcbf seq: x flg: x tail: xcbf frmt: x chkval: xe type: x=DATA SEGMENT HEADER UNLIMITED Extent Control Header
Extent Header:: spare: spare: #extents: #blocks: last map x #maps: offset: Highwater:: xcc ext#: blk#: ext size: #blocks in seg hdrs freelists: #blocks below: mapblk x offset: Unlocked Map Header:: next x #extents: obj#: flag: x Extent Map
xcc length:
nfl = nfb = typ = nxf = ccnt = SEG LST:: flg: USED lhd: xcc ltl: xcc End dump data blocks tsn: file#: minblk maxblk
SQL> select to_number(c xxx) from dual;
TO_NUMBER(CXXX)
提交后高水位移动到了第块
使用直接路径插入时会将数据插入到高水位以上的数据块中不会扫描 freelist 中的空闲块此时我再正常插入一条数据呢？应该插入到 块因为此时 块还没有装满测试一下
SQL> insert into test_block select * from dba_objects where rownum = ;
已创建 行
SQL> select dbms_rowidrowid_block_number(rowid) block# from test_block;
BLOCK#
已选择行 SQL> rollback; 回退已完成
如果直接路径插入后再回滚呢？高水位会发生什么样的变化？
SQL> insert/*+ append */ into test_block select * from dba_objects where rownum = ;
已创建 行
SQL> rollback;
回退已完成
SQL> alter system dump datafile block ;
系统已更改
*** :: Start dump data blocks tsn: file#: minblk maxblk buffer tsn: rdba: xcc (/) scn: xbfcbf seq: x flg: x tail: xcbf frmt: x chkval: xe type: x=DATA SEGMENT HEADER UNLIMITED Extent Control Header
Extent Header:: spare: spare: #extents: #blocks: last map x #maps: offset: Highwater:: xcc ext#: blk#: ext size: #blocks in seg hdrs freelists: #blocks below: mapblk x offset: Unlocked Map Header:: next x #extents: obj#: flag: x Extent Map
xcc length:
nfl = nfb = typ = nxf = ccnt = SEG LST:: flg: USED lhd: xcc ltl: xcc End dump data blocks tsn: file#: minblk maxblk 高水位没有发生变化仍然在第块而 append 一条数据后提交高水位会再移动到第块
SQL> insert/*+ append */ into test_block select * from dba_objects where rownum = ;
已创建 行
SQL> commit;
提交完成
SQL> alter system dump datafile block ;
系统已更改
*** :: Start dump data blocks tsn: file#: minblk maxblk buffer tsn: rdba: xcc (/) scn: xbff seq: x flg: x tail: xf frmt: x chkval: x type: x=DATA SEGMENT HEADER UNLIMITED Extent Control Header
Extent Header:: spare: spare: #extents: #blocks: last map x #maps: offset: Highwater:: xcc ext#: blk#: ext size: #blocks in seg hdrs freelists: #blocks below: mapblk x offset: Disk Lock:: Locked by xid: xed Map Header:: next x #extents: obj#: flag: x Extent Map
xcc length:
nfl = nfb = typ = nxf = ccnt = SEG LST:: flg: USED lhd: xcc ltl: xcc End dump data blocks tsn: file#: minblk maxblk
看看此时表 test_block 的数据分布
SQL> select dbms_rowidrowid_block_number(rowid) block# from test_block;
BLOCK#
已选择行
小结 append 数据时会从 Highwater 之后的数据块开始直接插入数据 append 数据后在提交之前不会升高 Highwater 的位置提交后才升高 Highwater |
