======================================================================================================================================================
第二天,重新总结了一下,怀疑跟段空间管理方式有关。随做如下实验:
一、在SEGMENT SPACE MANAGEMENT=MANUAL的SYSTEM表空间下建表:
1、建表sys.t4
SYS@PROD>create table t4(x int, y int);
Table created.
SYS@PROD>select tablespace_name,block_size,extent_management,segment_space_management from dba_tablespaces where tablespace_name='SYSTEM';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN SEGMEN
------------------------------ ---------- ---------- ------
SYSTEM 8192 LOCAL MANUAL
SYS@PROD>select obj# from obj$ where name='T4';
OBJ#
----------
13587
SYS@PROD>alter session set events 'immediate trace name buffers level 1';
Session altered.
--ONLY ONE BH FOR SEGMENT HEADER
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 186,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: [0x8403d820,0x8403d820] lru: [0x70beb6f0,0x70beb490]
obj-flags: object_ckpt_list
ckptq: [0x70fe5118,0x70beb868] fileq: [0x70beb288,0x83c41eb0] objq: [0x7a86b0c8,0x7a86b0c8]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xf.21377.0] LSCN: [0x0.79efd] HSCN: [0x0.79efd] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
此时只有一个segment header block header。
2、向sys.t4表中插入一条记录
SYS@PROD>insert into t4 values(1,2);
1 row created.
SYS@PROD>commit;
Commit complete.
SYS@PROD>alter session set events 'immediate trace name buffers level 1';
Session altered.
SYS@PROD>select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from t4;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
1 32401
--2 BH: 1 FOR SEGMENT HEADER 1 FOR DATA BLOCK. what des pwc means?
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 186,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: [0x8403d820,0x8403d820] lru: [0x70beb6f0,0x70beb490]
obj-flags: object_ckpt_list
ckptq: [0x70fe5118,0x70beb868] fileq: [0x70beb288,0x83c41eb0] objq: [0x7a86b0c8,0x7a86b0c8]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xf.21377.0] LSCN: [0x0.79efd] HSCN: [0x0.79efd] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 199,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: [0x8403d820,0x8403d820] lru: [0x70beb6f0,0x70beb490]
obj-flags: object_ckpt_list
ckptq: [0x70fe5118,0x83c41e90] fileq: [0x70beb288,0x83c41eb0] objq: [0x7a86b0c8,0x7a86b0c8]
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: [0xf.21377.0] LSCN: [0x0.79efd] HSCN: [0x0.79fdb] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x707d8148) file#: 1 rdba: 0x00407e91 (1/32401) class: 1 ba: 0x70404000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 184,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: [0x841f5600,0x841f5600] lru: [0x707d8360,0x843ead88]
obj-flags: object_ckpt_list
ckptq: [0x707d91e8,0x70fee328] fileq: [0x843ef670,0x757d8f98] objq: [0x7a868ef0,0x7a868ef0]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xf.21a2a.0] LSCN: [0x0.79fdb] HSCN: [0x0.79fdb] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
可以发现多了一个CLASS=1的DATA BLOCK HEADER,且仅有一个。
3、删表
SYS@PROD>drop table t4 purge;
Table dropped.
SYS@PROD>alter session set events 'immediate trace name buffers level 1';
Session altered.
-- see what happed below:
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 186,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: [0x8403d820,0x8403d820] lru: [0x70beb6f0,0x70beb490]
obj-flags: object_ckpt_list
ckptq: [0x70fe5118,0x70beb868] fileq: [0x70beb288,0x83c41eb0] objq: [0x7a86b0c8,0x7a86b0c8]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xf.21377.0] LSCN: [0x0.79efd] HSCN: [0x0.79efd] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 199,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: [0x8403d820,0x8403d820] lru: [0x70beb6f0,0x70beb490]
obj-flags: object_ckpt_list
ckptq: [0x70fe5118,0x83c41e90] fileq: [0x70beb288,0x83c41eb0] objq: [0x7a86b0c8,0x7a86b0c8]
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: [0xf.21377.0] LSCN: [0x0.79efd] HSCN: [0x0.79fdb] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x707d8148) file#: 1 rdba: 0x00407e91 (1/32401) class: 1 ba: 0x70404000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 184,28
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: [0x841f5600,0x841f5600] lru: [0x707d8360,0x843ead88]
obj-flags: object_ckpt_list
ckptq: [0x707d91e8,0x70fee328] fileq: [0x843ef670,0x757d8f98] objq: [0x7a868ef0,0x7a868ef0]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xf.21a2a.0] LSCN: [0x0.79fdb] HSCN: [0x0.79fdb] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x707e1358) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x704fa000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 200,19
dbwrid: 0 obj: 13587 objn: 0 tsn: 0 afn: 1 hint: f
hash: [0x70beb588,0x8403d820] lru: [0x60fe1310,0x843eb468]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x70beb4d8) file#: 1 rdba: 0x00407e90 (1/32400) class: 4 ba: 0x70a0a000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 205,19
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: [0x8403d820,0x707e1408] lru: [0x60ff3730,0x843ec208]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x707d8148) file#: 1 rdba: 0x00407e91 (1/32401) class: 1 ba: 0x70404000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 190,19
dbwrid: 0 obj: 13587 objn: 13587 tsn: 0 afn: 1 hint: f
hash: [0x841f5600,0x841f5600] lru: [0x60fd8100,0x843ead98]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
二、在SEGMENT SPACE MANAGEMENT=AUTO的EXAMPLE空间下建表:
1、建表zn.t8
SYS@PROD>create table zn.t8(x int, y int);
Table created.
SYS@PROD>select obj# from obj$ where name='T8';
OBJ#
----------
13591
SYS@PROD>alter session set events 'immediate trace name buffers level 1';
Session altered.
SYS@PROD>select * from v$diag_info where name='Default Trace File';
INST_ID NAME VALUE
---------- ---------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Default Trace File /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_7243.trc
vi /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_7243.trc
找不到objn: 13591的BH
同样发现此时在DBA_SEGMENTS和ZN.USER_SEGMENTS里都找不到SEGMENT_NAME='T8'的记录
2、插入一条记录zn.t8
SYS@PROD>insert into zn.t8 values(1,2);
1 row created.
SYS@PROD>select segment_name,tablespace_name from dba_segments where segment_name='T8';
SEGMENT_NA TABLESPACE_NAME
---------- ------------------------------
T8 EXAMPLE
SYS@PROD>roll back;
Rollback complete.
SYS@PROD>select segment_name,tablespace_name from dba_segments where segment_name='T8';
SEGMENT_NA TABLESPACE_NAME
---------- ------------------------------
T8 EXAMPLE
此时再做dump查看并查看trace file
SYS@PROD>alter session set events 'immediate trace name buffers level 1';
Session altered.
发现如下出现8个BH,对应的OBJECT_ID=13591-----ZN.T8!重现了我的帖子问题。但是此时我就可以理解一些了。
自动段空间管理预分配了5个DATA BLOCK,再加上1个SEGMENT HEADER和CLASS=8/9的两个块(用处不明),合计8个。
即使我上面的插入没有提交,做了回滚,依然分配了5个DATA BLOCK。
最后重新做插入和提交,直接将ROW放在了229号块上。
BH (0x6fbe8428) file#: 4 rdba: 0x010000e1 (4/225) class: 9 ba: 0x6f9b8000
set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 205,28
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: [0x84038500,0x84038500] lru: [0x6fbe8640,0x6fbe83e0]
obj-flags: object_ckpt_list
ckptq: [0x6fbe82f8,0x6fbe8a18] fileq: [0x83c3a2e0,0x83c3a2e0] objq: [0x7a86bca8,0x6fbe82d8]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xf.26017.0] LSCN: [0x0.7a87c] HSCN: [0x0.7a87c] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x753e7128) file#: 4 rdba: 0x010000e6 (4/230) class: 1 ba: 0x75198000
set: 9 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 196,28
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: [0x840786e0,0x840786e0] lru: [0x743f6580,0x843ead88]
obj-flags: object_ckpt_list
ckptq: [0x843ec410,0x6fbf6bb8] fileq: [0x843ec490,0x843ec490] objq: [0x7a868770,0x7a868770]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xf.26017.0] LSCN: [0x0.7a886] HSCN: [0x0.7a886] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x6fbdeaf8) file#: 4 rdba: 0x010000e3 (4/227) class: 1 ba: 0x6f8b6000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 206,19
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: [0x840e00c0,0x840e00c0] lru: [0x6fbded10,0x6fbdeab0]
obj-flags: object_ckpt_list
ckptq: [0x6fbde9c8,0x703eedd8] fileq: [0x6fbde9d8,0x703eede8] objq: [0x6fbded38,0x6fbdead8]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xf.26017.0] LSCN: [0x0.7a886] HSCN: [0x0.7a886] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x6fbf2df8) file#: 4 rdba: 0x010000e0 (4/224) class: 8 ba: 0x6fad6000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 215,19
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: [0x73ff9f78,0x84148720] lru: [0x6fbf3010,0x6fbf2db0]
obj-flags: object_ckpt_list
ckptq: [0x6fbf2cc8,0x6ffe74b8] fileq: [0x83c41f10,0x83c41f10] objq: [0x7a86a9e8,0x6fbf2ca8]
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: [0xf.26017.0] LSCN: [0x0.7a87d] HSCN: [0x0.7a886] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x6fbf2b98) file#: 4 rdba: 0x010000e5 (4/229) class: 1 ba: 0x6fad2000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 215,19
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: [0x84188900,0x84188900] lru: [0x6fbf2db0,0x843ec1f8]
obj-flags: object_ckpt_list
ckptq: [0x83c450d0,0x703dfa68] fileq: [0x83c45150,0x83c45150] objq: [0x6fbf2f08,0x7a86a9e8]
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty redo_since_read
LRBA: [0xf.26017.0] LSCN: [0x0.7a886] HSCN: [0x0.7a892] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x6fbdec28) file#: 4 rdba: 0x010000e2 (4/226) class: 4 ba: 0x6f8b8000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 206,19
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: [0x841f02e0,0x841f02e0] lru: [0x6fbdee40,0x6fbdebe0]
obj-flags: object_ckpt_list
ckptq: [0x843f7280,0x77fe8a18] fileq: [0x843f7300,0x77fe8a28] objq: [0x7a865b80,0x6fbdec08]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xf.26017.0] LSCN: [0x0.7a87b] HSCN: [0x0.7a886] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x6fbde9c8) file#: 4 rdba: 0x010000e7 (4/231) class: 1 ba: 0x6f8b4000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 206,19
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: [0x842304c0,0x842304c0] lru: [0x6fbdebe0,0x843eb458]
obj-flags: object_ckpt_list
ckptq: [0x843fa4c0,0x6fbdeaf8] fileq: [0x843fa540,0x6fbdeb08] objq: [0x6fbdec08,0x7a865b80]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xf.26017.0] LSCN: [0x0.7a886] HSCN: [0x0.7a886] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x6fbe81c8) file#: 4 rdba: 0x010000e4 (4/228) class: 1 ba: 0x6f9b4000
set: 11 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 205,28
dbwrid: 0 obj: 13591 objn: 13591 tsn: 4 afn: 4 hint: f
hash: [0x84298b20,0x84298b20] lru: [0x6fbe83e0,0x843ebb28]
obj-flags: object_ckpt_list
ckptq: [0x83c37020,0x6ffd9908] fileq: [0x83c370a0,0x83c370a0] objq: [0x6fbe8538,0x7a86bca8]
st: XCURRENT md: NULL tch: 1
flags: buffer_dirty redo_since_read
LRBA: [0xf.26017.0] LSCN: [0x0.7a886] HSCN: [0x0.7a886] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
SYS@PROD>select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from zn.t8;
no rows selected
SYS@PROD>insert into zn.t8 values(1,2);
1 row created.
SYS@PROD>commit;
Commit complete.
SYS@PROD>select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from zn.t8;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
4 229
不知上面我的理解和实验过程是否正确,请各位大侠指正。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26521853/viewspace-1137844/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26521853/viewspace-1137844/