背景
了解表段的高水位线与不同状态数据块的关系，以及高水位线一些相关概念；且熟悉哪些字典或包及过程与此相关。
结论
1,oradebug形成的TRC文件中的HWM我没有研究明白，这块比较深，还需梳理思路再战
2，表的高水位线采用dbms_space.unsed_space获取
3，表的高水位线=total blocks - total unused blocks +1
4, 表仅插入时，其total unused blocks为0，仅DELETE后此参数才会为非0值
5，last used block为最新分配的区的数据块个数
6, alter table move或者alter table shrink space可以在删除表的记录后，下降表的高水位线
7, alter table shrink space compact即使在表删除记录后，仍不会下降表的高水位线
测试
1,数据库版本SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
2，创建测试表
SQL> create table t_hwm(a int,b int);
Table created.
3，插入数据
SQL> insert into t_hwm values(1,1);
1 row created.
SQL> commit;
Commit complete.
4，获取表段所属文件及段头数据块
SQL> select segment_name,header_file,header_block,bytes,blocks from dba_segments where lower(segment_name)='t_hwm';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BYTES BLOCKS
------------------------------ ----------- ------------ ---------- ----------
T_HWM 4 85442 65536 8
5，转储表段头数据块
SQL> oradebug setmypid;
ORA-01031: insufficient privileges
SQL> show user
USER is "SCOTT"
SQL> conn /as sysdba
Connected.
SQL> oradebug setmypid;
Statement processed.
SQL> alter system dump datafile 4 block 85442;
System altered.
SQL> oradebug tracefile_name;
/oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_28025.trc
SQL>
6，获取转储文件TRC的高水位线
[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_28025.trc|grep -i --color high
Highwater:: 0x01014dc8 ext#: 0 blk#: 8 ext size: 8
Low HighWater Mark :
Highwater:: 0x01014dc8 ext#: 0 blk#: 8 ext size: 8
Level 1 BMB for High HWM block: 0x01014dc0
7，再次插入数据，看下高水位线会不会变化
SQL> insert into scott.t_hwm select * from scott.t_hwm;
1 row created.
SQL> commit;
Commit complete.
可见插入数据量小时，高水位线是不会变化的
[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_2943.trc|grep -i --color high
Highwater:: 0x01014dc8 ext#: 0 blk#: 8 ext size: 8
Low HighWater Mark :
Highwater:: 0x01014dc8 ext#: 0 blk#: 8 ext size: 8
Level 1 BMB for High HWM block: 0x01014dc0
也就是说只要插入数据量到达一定情况，高水位线才会发生变化,所以我们继续插入数据，直接分配新的区
SQL> select count(*) from dba_extents where segment_name='T_HWM';
COUNT(*)
----------
1
SQL> insert into scott.t_hwm select * from scott.t_hwm;
2048 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from dba_extents where segment_name='T_HWM';
COUNT(*)
----------
2
可见即使分配第2个区，高水位线仍不会变化
[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_6326.trc|grep -i --color high
Highwater:: 0x01014dc8 ext#: 0 blk#: 8 ext size: 8
Low HighWater Mark :
Highwater:: 0x01014dc8 ext#: 0 blk#: 8 ext size: 8
Level 1 BMB for High HWM block: 0x01014dc0
8，继续插入数据，可见只要插入数据到达一定程度，表段的高水位线才会推进和变化
SQL> insert into scott.t_hwm select * from scott.t_hwm;
16384 rows created.
SQL> select count(*) from dba_extents where segment_name='T_HWM';
COUNT(*)
----------
7
SQL> commit;
Commit complete.
[oracle@seconary ~]$ more /oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_9443.trc|grep -i --color high
Highwater:: 0x01014dd0 ext#: 1 blk#: 8 ext size: 8
Low HighWater Mark :
Highwater:: 0x01014dd0 ext#: 1 blk#: 8 ext size: 8
Level 1 BMB for High HWM block: 0x01014dc0
9，现在出现一个问题，到底表段的数据插入到什么程度后，其高水位线才会推进和变化呢？
---表所有者及相关，要用大写，否则报错
create or replace procedure proc_show_space
as
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_last_used_extent_file_id number;
v_last_used_extent_block_id number;
v_last_used_block number;
begin
dbms_space.unused_space('SCOTT','T_HWM','TABLE',v_total_blocks,v_total_bytes,v_unused_blocks,v_unused_bytes,v_last_used_extent_file_id,v_last_used_extent_block_id,v_last_used_block);
dbms_output.put_line('total blocks ..............'||v_total_blocks);
dbms_output.put_line('total unused blocks .......'||v_unused_blocks);
dbms_output.put_line('last used block............'||v_last_used_block);
end;
/
SQL> select extent_id,blocks from dba_extents where segment_name='T_HWM';
EXTENT_ID BLOCKS
---------- ----------
0 8
1 8
2 8
3 8
4 8
5 8
6 8
7 rows selected.
SQL> exec proc_show_space;
total blocks ..............56
total unused blocks .......0
last used block............8
PL/SQL procedure successfully completed.
可见此时高水位线=total blocks - total unused blocks +1=57
SQL> insert into scott.t_hwm select * from scott.t_hwm;
32768 rows created.
SQL> commit;
Commit complete.
SQL> exec proc_show_space;
total blocks ..............112
total unused blocks .......0
last used block............8
PL/SQL procedure successfully completed.
可见继续插入记录后，此时高水位线=total blocks - total unused blocks +1=113
删除表记录表的高水位线不会下降
SQL> delete from scott.t_hwm where rownum<=3000;
3000 rows deleted.
SQL> commit;
Commit complete.
SQL> exec proc_show_space;
total blocks ..............112
total unused blocks .......0
last used block............8
PL/SQL procedure successfully completed.
继续插入记录，验证下last used block的含义,可见last used block即最新分配区的数据块个数
SQL> select count(*) from scott.t_hwm;
COUNT(*)
----------
1000576
SQL> select extent_id,blocks from dba_extents where segment_name='T_HWM';
EXTENT_ID BLOCKS
---------- ----------
0 8
1 8
2 8
3 8
4 8
5 8
6 8
7 8
8 8
9 8
10 8
EXTENT_ID BLOCKS
---------- ----------
11 8
12 8
13 8
14 8
15 8
16 128
17 128
18 128
19 128
20 128
21 128
EXTENT_ID BLOCKS
---------- ----------
22 128
23 128
24 128
25 128
26 128
27 128
28 rows selected.
SQL> exec proc_show_space;
total blocks ..............1664
total unused blocks .......0
last used block............128
PL/SQL procedure successfully completed.
表MOVE后高水线下降
SQL> alter table scott.t_hwm move;
Table altered.
SQL> exec proc_show_space;
total blocks ..............1664
total unused blocks .......107
last used block............21
PL/SQL procedure successfully completed.
高水位线=1664-107+1=158
除了表的MOVE可以下降表的高水位线，还有其它方法吗
SQL> delete from scott.t_hwm where rownum<=2000;
2000 rows deleted.
SQL> commit;
Commit complete.
SQL> exec proc_show_space;
total blocks ..............1664
total unused blocks .......107
last used block............21
PL/SQL procedure successfully completed.
SQL> alter table scott.t_hwm shrink space;
alter table scott.t_hwm shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled
可见通过alter table shrink space也可以下降表的高水位线，但前提先要打开表的行移动，这时就会影响表的索引可用性
SQL> alter table scott.t_hwm enable row movement;
Table altered.
SQL> alter table scott.t_hwm shrink space;
Table altered.
SQL> exec proc_show_space;
total blocks ..............1560
total unused blocks .......6
last used block............18
PL/SQL procedure successfully completed.
可见alter table shrink space compact不会下降表的高水位线
SQL> delete from scott.t_hwm where rownum<=100;
100 rows deleted.
SQL> commit;
Commit complete.
SQL> exec proc_show_space;
total blocks ..............1560
total unused blocks .......6
last used block............18
PL/SQL procedure successfully completed.
SQL> alter table scott.t_hwm shrink space compact;
Table altered.
SQL> exec proc_show_space;
total blocks ..............1560
total unused blocks .......6
last used block............18
PL/SQL procedure successfully completed.
转自 http://blog.itpub.net/9240380/viewspace-1814711/