ORACLE空間管理實驗5：塊管理之ASSM下高水位的影響--刪除和查詢
高水位概念：
所有的oracle段(segments，在此，為了理解方便，建議把segment作為表的一個同義詞) 都有一個在段內容納資料的上限，我們把這個上限稱為"high water mark"或HWM。這個HWM是一個標記，用來說明已經有多少沒有使用的資料塊分配給這個segment。HWM原則上HWM只會增大，不會縮小，即使將表中的資料全部刪除，HWM還是為原值，由於這個特點，使HWM很象一個水庫的歷史最高水位，這也就是HWM的原始含義。
這個概念百度下一大把，可以參考：
http://www.blogjava.net/decode360/archive/2009/07/14/287767.html
http://www.cnblogs.com/linjiqin/archive/2012/01/15/2323030.html
高水位與低高水位：低高水位與高水位之間存在的資料塊的狀態可能是未格式化或格式的。低高水位以下的是格式化了的，可以被使用。
HWM對資料庫的操作有如下影響：
1.全表掃描通常要讀出直到HWM標記的所有的屬於該表資料庫塊，即使該表中沒有任何資料。
3.即使HWM以下有空閒的資料庫塊，鍵入在插入資料時使用了append關鍵字，則在插入時使用HWM以上的資料塊，此時HWM會自動增大。
2.插入時，預設只能插入到高水位以下的資料塊，這在高併發時可能引起熱塊從而導致效能問題。
下面就通過實驗來驗證一下:
Table created.
BYS@ bys3>insert into test12 select * from dba_objects;
17558 rows created.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>insert into test12 select * from test12;
35116 rows created.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>insert into test12 select * from test12;
70232 rows created.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>insert into test12 select * from test12;
140464 rows created.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>alter system checkpoint; ---要做檢查點
System altered.
BYS@ bys3>select header_block,header_file from dba_segments where segment_name='TEST12' and owner='BYS';
HEADER_BLOCK HEADER_FILE
------------ -----------
922 4
BYS@ bys3>select sum(blocks) from dba_extents where segment_name='TEST12' and owner='BYS';
SUM(BLOCKS)
-----------
3840
BYS@ bys3>select count(extent_id) from dba_extents where segment_name='TEST12' and owner='BYS';
COUNT(EXTENT_ID)
----------------
45
多執行幾次，取後面的平均值：
BYS@ bys3>set autotrace traceonly stat
BYS@ bys3>select * from test12;
280960 rows selected.
Elapsed: 00:00:05.70
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23504 consistent gets
3710 physical reads
0 redo size
29190599 bytes sent via SQL*Net to client
206449 bytes received via SQL*Net from client
18732 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
280960 rows processed
BYS@ bys3>alter system dump datafile 4 block 922;
System altered.
BYS@ bys3>select value from v$diag_info where name like 'De%';
VALUE
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_25994.trc
###############DUMP段頭，檢視高水位資訊：Highwater:: 0x01001180 --4480號塊
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 45 #blocks: 3840
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01002100 ext#: 44 blk#: 128 ext size: 128 --高水位的DBA：0x01002100，8448號塊
#blocks in seg. hdr's freelists: 0
#blocks below: 3784
mapblk 0x00000000 offset: 44
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01002080 ext#: 43 blk#: 128 ext size: 128 --低高水位 0x01002080
#blocks in seg. hdr's freelists: 0
#blocks below: 3712
mapblk 0x00000000 offset: 43
Level 1 BMB for High HWM block: 0x01002081
Level 1 BMB for Low HWM block: 0x01002001
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01000399
Last Level 1 BMB: 0x01002081
Last Level II BMB: 0x01000399
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 45 obj#: 23303 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01000398 length: 8
%……………………
0x01002080 length: 128 --最後一個L1 DBA是8320號塊，管理了128個塊，高水位是8448號塊，剛好是最後一個L1的最後一個資料塊。
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000398 Data dba: 0x0100039b
……………………
Extent 44 : L1 dba: 0x01002080 Data dba: 0x01002082 高水位應該就是這個L1管理的最後一個塊：0x01002082 -8322號塊，
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000399
Table analyzed.
BYS@ bys3>select pct_free,pct_used,blocks,avg_row_len,chain_cnt from tabs where table_name='TEST12';
PCT_FREE PCT_USED BLOCKS AVG_ROW_LEN CHAIN_CNT
---------- ---------- ---------- ----------- ----------
10 3784 93 0
BYS@ bys3>set autotrace traceonly stat
BYS@ bys3>select * from test12;
280960 rows selected.
Elapsed: 00:00:05.46
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22204 consistent gets
0 physical reads
0 redo size
29190599 bytes sent via SQL*Net to client
206449 bytes received via SQL*Net from client
18732 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
280960 rows processed
DUMP資訊與未做表分析時相同，不貼了。
######################
280944 rows deleted.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>select header_block,header_file from dba_segments where segment_name='TEST12' and owner='BYS';
HEADER_BLOCK HEADER_FILE
------------ -----------
922 4
BYS@ bys3>select sum(blocks) from dba_extents where segment_name='TEST12' and owner='BYS';
SUM(BLOCKS)
-----------
3840
BYS@ bys3>select count(extent_id) from dba_extents where segment_name='TEST12' and owner='BYS';
COUNT(EXTENT_ID)
----------------
System altered.
BYS@ bys3>select value from v$diag_info where name like 'De%';
VALUE
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_26054.trc
BYS@ bys3>set autotrace traceonly stat
BYS@ bys3>select * from test12;
no rows selected
Elapsed: 00:00:00.02
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3721 consistent gets
2 physical reads
0 redo size
1183 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
###############DELETE刪除後的DUMP資訊和上一個未刪除時的沒啥區別，不貼了。
Table altered.
BYS@ bys3>alter table test12 shrink space; --SHRINK與MOVE，詳見：shrink合併資料塊--解決資料塊碎片問題
Table altered.
BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>select pct_free,pct_used,blocks,avg_row_len,chain_cnt from tabs where table_name='TEST12';
PCT_FREE PCT_USED BLOCKS AVG_ROW_LEN CHAIN_CNT
---------- ---------- ---------- ----------- ----------
10 3784 0 0
BYS@ bys3>analyze table test12 compute statistics; 對錶做分析後，tabs裡的BLOCKS資訊才會變。
Table analyzed.
BYS@ bys3>select pct_free,pct_used,blocks,avg_row_len,chain_cnt from tabs where table_name='TEST12';
PCT_FREE PCT_USED BLOCKS AVG_ROW_LEN CHAIN_CNT
---------- ---------- ---------- ----------- ----------
10 1 0 0
BYS@ bys3>alter system dump datafile 4 block 922;
System altered.
BYS@ bys3>select value from v$diag_info where name like 'De%';
VALUE
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_26432.trc
#########################DUMP資訊如下：--可以看到空間已經回收了。高水位也已經下降
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0100039c ext#: 0 blk#: 4 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x0100039c ext#: 0 blk#: 4 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x01000398
Level 1 BMB for Low HWM block: 0x01000398
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01000399
Last Level 1 BMB: 0x01000398
Last Level II BMB: 0x01000399
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 23303 flag: 0x10000000
Inc # 1
Extent Map
-----------------------------------------------------------------
0x01000398 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000398 Data dba: 0x0100039b
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000399
End dump data blocks tsn: 4 file#: 4 minblk 922 maxblk 922
##################################
BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>alter system dump datafile 4 block 466;
System altered.
#############DUMP檔案資訊：
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 17 #blocks: 256
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x010011f5 ext#: 16 blk#: 117 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 245
mapblk 0x00000000 offset: 16
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x010011f5 ext#: 16 blk#: 117 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 245
mapblk 0x00000000 offset: 16
Level 1 BMB for High HWM block: 0x01001181
Level 1 BMB for Low HWM block: 0x01001181
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x010001d1
Last Level 1 BMB: 0x01001181
Last Level II BMB: 0x010001d1
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 17 obj#: 23300 flag: 0x10000000
Inc # 0
Extent Map
做TRUNCATE操作然後DUMP段頭
BYS@ bys3>truncate table test13;
Table truncated.
BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>alter system dump datafile 4 block 466;
System altered.
#########################
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x010001d3 ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Disk Lock:: Locked by xid: 0x0002.019.00001354
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x010001d3 ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x010001d0
Level 1 BMB for Low HWM block: 0x010001d0
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x010001d1
Last Level 1 BMB: 0x010001d0
Last Level II BMB: 0x010001d1
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 23304 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x010001d0 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x010001d0 Data dba: 0x010001d3
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x010001d1
所有的oracle段(segments，在此，為了理解方便，建議把segment作為表的一個同義詞) 都有一個在段內容納資料的上限，我們把這個上限稱為"high water mark"或HWM。這個HWM是一個標記，用來說明已經有多少沒有使用的資料塊分配給這個segment。HWM原則上HWM只會增大，不會縮小，即使將表中的資料全部刪除，HWM還是為原值，由於這個特點，使HWM很象一個水庫的歷史最高水位，這也就是HWM的原始含義。
這個概念百度下一大把，可以參考：
http://www.blogjava.net/decode360/archive/2009/07/14/287767.html
http://www.cnblogs.com/linjiqin/archive/2012/01/15/2323030.html
高水位與低高水位：低高水位與高水位之間存在的資料塊的狀態可能是未格式化或格式的。低高水位以下的是格式化了的，可以被使用。
HWM對資料庫的操作有如下影響：
1.全表掃描通常要讀出直到HWM標記的所有的屬於該表資料庫塊，即使該表中沒有任何資料。
3.即使HWM以下有空閒的資料庫塊，鍵入在插入資料時使用了append關鍵字，則在插入時使用HWM以上的資料塊，此時HWM會自動增大。
2.插入時，預設只能插入到高水位以下的資料塊，這在高併發時可能引起熱塊從而導致效能問題。
下面就通過實驗來驗證一下:
全表掃描通常要讀出直到低高水位HWM標記的所有的屬於該表資料庫塊，即使該表中沒有任何資料--DELETE來測試。TRUNCATE會釋放。
解決方法有：expdp/impdp，shrink：需要在ASSM且表開啟行移動。MOVE不支援線上，或者CRTEATE TABLE A AS，再刪除原表，將新表改為原名。。
1. DELETE不會改變高水位,但是刪除後對錶做SHRINK操作可以回收空間，降低高水位
BYS@ bys3>create table test12 as select * from dba_objects;Table created.
BYS@ bys3>insert into test12 select * from dba_objects;
17558 rows created.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>insert into test12 select * from test12;
35116 rows created.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>insert into test12 select * from test12;
70232 rows created.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>insert into test12 select * from test12;
140464 rows created.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>alter system checkpoint; ---要做檢查點
System altered.
BYS@ bys3>select header_block,header_file from dba_segments where segment_name='TEST12' and owner='BYS';
HEADER_BLOCK HEADER_FILE
------------ -----------
922 4
BYS@ bys3>select sum(blocks) from dba_extents where segment_name='TEST12' and owner='BYS';
SUM(BLOCKS)
-----------
3840
BYS@ bys3>select count(extent_id) from dba_extents where segment_name='TEST12' and owner='BYS';
COUNT(EXTENT_ID)
----------------
45
多執行幾次，取後面的平均值：
BYS@ bys3>set autotrace traceonly stat
BYS@ bys3>select * from test12;
280960 rows selected.
Elapsed: 00:00:05.70
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23504 consistent gets
3710 physical reads
0 redo size
29190599 bytes sent via SQL*Net to client
206449 bytes received via SQL*Net from client
18732 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
280960 rows processed
BYS@ bys3>alter system dump datafile 4 block 922;
System altered.
BYS@ bys3>select value from v$diag_info where name like 'De%';
VALUE
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_25994.trc
###############DUMP段頭，檢視高水位資訊：Highwater:: 0x01001180 --4480號塊
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 45 #blocks: 3840
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01002100 ext#: 44 blk#: 128 ext size: 128 --高水位的DBA：0x01002100，8448號塊
#blocks in seg. hdr's freelists: 0
#blocks below: 3784
mapblk 0x00000000 offset: 44
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01002080 ext#: 43 blk#: 128 ext size: 128 --低高水位 0x01002080
#blocks in seg. hdr's freelists: 0
#blocks below: 3712
mapblk 0x00000000 offset: 43
Level 1 BMB for High HWM block: 0x01002081
Level 1 BMB for Low HWM block: 0x01002001
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01000399
Last Level 1 BMB: 0x01002081
Last Level II BMB: 0x01000399
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 45 obj#: 23303 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01000398 length: 8
%……………………
0x01002080 length: 128 --最後一個L1 DBA是8320號塊，管理了128個塊，高水位是8448號塊，剛好是最後一個L1的最後一個資料塊。
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000398 Data dba: 0x0100039b
……………………
Extent 44 : L1 dba: 0x01002080 Data dba: 0x01002082 高水位應該就是這個L1管理的最後一個塊：0x01002082 -8322號塊，
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000399
對錶做分析後
BYS@ bys3>analyze table test12 compute statistics;Table analyzed.
BYS@ bys3>select pct_free,pct_used,blocks,avg_row_len,chain_cnt from tabs where table_name='TEST12';
PCT_FREE PCT_USED BLOCKS AVG_ROW_LEN CHAIN_CNT
---------- ---------- ---------- ----------- ----------
10 3784 93 0
BYS@ bys3>set autotrace traceonly stat
BYS@ bys3>select * from test12;
280960 rows selected.
Elapsed: 00:00:05.46
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22204 consistent gets
0 physical reads
0 redo size
29190599 bytes sent via SQL*Net to client
206449 bytes received via SQL*Net from client
18732 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
280960 rows processed
DUMP資訊與未做表分析時相同，不貼了。
######################
DELETE刪除表中全部資料
BYS@ bys3>delete test12;280944 rows deleted.
BYS@ bys3>commit;
Commit complete.
BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>select header_block,header_file from dba_segments where segment_name='TEST12' and owner='BYS';
HEADER_BLOCK HEADER_FILE
------------ -----------
922 4
BYS@ bys3>select sum(blocks) from dba_extents where segment_name='TEST12' and owner='BYS';
SUM(BLOCKS)
-----------
3840
BYS@ bys3>select count(extent_id) from dba_extents where segment_name='TEST12' and owner='BYS';
COUNT(EXTENT_ID)
----------------
45
BYS@ bys3>analyze table test12 compute statistics;
Table analyzed.
BYS@ bys3>select pct_free,pct_used,blocks,avg_row_len,chain_cnt from tabs where table_name='TEST12';
PCT_FREE PCT_USED BLOCKS AVG_ROW_LEN CHAIN_CNT
---------- ---------- ---------- ----------- ----------
10 3784 0 0
System altered.
BYS@ bys3>select value from v$diag_info where name like 'De%';
VALUE
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_26054.trc
BYS@ bys3>set autotrace traceonly stat
BYS@ bys3>select * from test12;
no rows selected
Elapsed: 00:00:00.02
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3721 consistent gets
2 physical reads
0 redo size
1183 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
###############DELETE刪除後的DUMP資訊和上一個未刪除時的沒啥區別，不貼了。
DELETE刪除後對錶做SHRINK：--可以回收空間，降低高水位
BYS@ bys3>alter table test12 enable row movement;Table altered.
BYS@ bys3>alter table test12 shrink space; --SHRINK與MOVE，詳見：shrink合併資料塊--解決資料塊碎片問題
Table altered.
BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>select pct_free,pct_used,blocks,avg_row_len,chain_cnt from tabs where table_name='TEST12';
PCT_FREE PCT_USED BLOCKS AVG_ROW_LEN CHAIN_CNT
---------- ---------- ---------- ----------- ----------
10 3784 0 0
BYS@ bys3>analyze table test12 compute statistics; 對錶做分析後，tabs裡的BLOCKS資訊才會變。
Table analyzed.
BYS@ bys3>select pct_free,pct_used,blocks,avg_row_len,chain_cnt from tabs where table_name='TEST12';
PCT_FREE PCT_USED BLOCKS AVG_ROW_LEN CHAIN_CNT
---------- ---------- ---------- ----------- ----------
10 1 0 0
BYS@ bys3>alter system dump datafile 4 block 922;
System altered.
BYS@ bys3>select value from v$diag_info where name like 'De%';
VALUE
----------------------------------------------------------------------------------------------------
/u01/diag/rdbms/bys3/bys3/trace/bys3_ora_26432.trc
#########################DUMP資訊如下：--可以看到空間已經回收了。高水位也已經下降
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x0100039c ext#: 0 blk#: 4 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x0100039c ext#: 0 blk#: 4 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x01000398
Level 1 BMB for Low HWM block: 0x01000398
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01000399
Last Level 1 BMB: 0x01000398
Last Level II BMB: 0x01000399
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 23303 flag: 0x10000000
Inc # 1
Extent Map
-----------------------------------------------------------------
0x01000398 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000398 Data dba: 0x0100039b
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000399
End dump data blocks tsn: 4 file#: 4 minblk 922 maxblk 922
##################################
2.TRUNCATE操作直接就可以回收空間，改變高水位--但是如果非分割槽表，沒有DELETE應用場景多。
create table test13 as select * from dba_objects;BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>alter system dump datafile 4 block 466;
System altered.
#############DUMP檔案資訊：
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 17 #blocks: 256
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x010011f5 ext#: 16 blk#: 117 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 245
mapblk 0x00000000 offset: 16
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x010011f5 ext#: 16 blk#: 117 ext size: 128
#blocks in seg. hdr's freelists: 0
#blocks below: 245
mapblk 0x00000000 offset: 16
Level 1 BMB for High HWM block: 0x01001181
Level 1 BMB for Low HWM block: 0x01001181
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x010001d1
Last Level 1 BMB: 0x01001181
Last Level II BMB: 0x010001d1
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 17 obj#: 23300 flag: 0x10000000
Inc # 0
Extent Map
################
做TRUNCATE操作然後DUMP段頭
BYS@ bys3>truncate table test13;Table truncated.
BYS@ bys3>alter system checkpoint;
System altered.
BYS@ bys3>alter system dump datafile 4 block 466;
System altered.
#########################
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x010001d3 ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Disk Lock:: Locked by xid: 0x0002.019.00001354
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x010001d3 ext#: 0 blk#: 3 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x010001d0
Level 1 BMB for Low HWM block: 0x010001d0
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x010001d1
Last Level 1 BMB: 0x010001d0
Last Level II BMB: 0x010001d1
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 23304 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x010001d0 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x010001d0 Data dba: 0x010001d3
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x010001d1
相關文章
- ORACLE空間管理實驗6：塊管理之ASSM下插入操作--高水位的影響及大併發插入的效能問題OracleSSM
- ORACLE空間管理實驗4：塊管理之ASSM三級點陣圖結構OracleSSM
- Oracle 自動段空間管理(ASSM)OracleSSM
- 表空間查詢和管理
- ORACLE空間管理實驗3：區管理之大區小區對I/O效能的影響Oracle
- ORACLE空間管理實驗7：塊管理之MMSM--為什麼SYSTEM/UNDO/TEMP是MMSM管理？Oracle
- assm:Oracle 10g的自動段空間管理SSMOracle 10g
- Oracle 10g的自動段空間管理（ASSM）Oracle 10gSSM
- ORACLE空間管理實驗2：區的管理與分配Oracle
- ORACLE高水位表的查詢方法Oracle
- 並行查詢對於響應時間的影響實驗並行
- Oracle 高水位查詢和處理方法彙總Oracle
- 2 Day DBA-管理Oracle例項-修改表空間-刪除表空間Oracle
- ORACLE空間管理實驗8：資料塊格式分析--DUMP結合BBEDOracle
- oracle 刪除表空間試驗面面觀Oracle
- 【TABLESPACE】Oracle資料庫預設永久表空間的查詢及刪除方法Oracle資料庫
- Oracle move和shrink釋放高水位空間Oracle
- Oracle中ASSM模式下,全表掃描的L3塊的邏輯讀的影響OracleSSM模式
- Oracle 表空間的管理Oracle
- Oracle的表空間管理Oracle
- oracle表空間查詢Oracle
- Oracle 本地表空間管理與字典表空間管理Oracle
- ORACLE表空間的建立修改刪除Oracle
- Oracle表空間管理Oracle
- Oracle 表空間管理Oracle
- oracle查詢表空間的空間佔用情況Oracle
- 關於oracle的空間查詢Oracle
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 刪除表空間和表空間包含的檔案
- Oracle下查詢臨時表空間佔用率Oracle
- oracle本地管理的表空間Oracle
- Oracle表空間的管理方式（LMT、DMT）--本地和字典管理Oracle
- ORACLE空間管理實驗1：探索LMT表空間管理下資料檔案頭的結構及點陣圖中區的記錄方式Oracle
- 偽造基於ASSM表空間的資料塊SSM
- DMT 模式下Oracle Extent空間管理 -- uet$ & fet$模式Oracle
- DMT 模式下Oracle Extent空間管理 -- uet$ & fet$模式Oracle
- oracle級聯刪除使用者，刪除表空間Oracle
- mysql刪除和更新操作對效能的影響MySql