Oracle HWM 测试

1.重新获取统计信息

declare 
begin
  DBMS_STATS.gather_table_stats('AA', 'T_TEST',degree => 3 , cascade => FALSE);
end;

2.创建一个procedure show_space 查看表空间中已经使用的块（字节），未使用的块（字节）

create or replace procedure show_space(

 p_segname in varchar2,
 p_owner in varchar2 default user,
 p_type in varchar2 default 'TABLE',
 p_partition in varchar2 default null)
as 
 l_total_blocks   number;
 l_total_bytes    number;
 l_unused_blocks  number;
 l_unused_bytes   number;
 l_LastUsedExtFileId   number;
 l_LastUsedExtBlockId  number;
 l_Last_used_block     number;


 procedure p(p_label in varchar2, p_num in number)
 is
 begin
   dbms_output.put_line(rpad(p_label,40,'.') || p_num);
 end;
begin
  dbms_space.unused_space (
  segment_owner   =>p_owner,
  segment_name    =>p_segname,
  segment_type    =>p_type,
  partition_name  =>p_partition,
  total_blocks    =>l_total_blocks,
  total_bytes     =>l_total_bytes,
  unused_blocks   =>l_unused_blocks,
  unused_bytes    =>l_unused_bytes,
  last_used_extent_file_id    =>l_LastUsedExtFileId,
  last_used_extent_block_id   =>l_LastUsedExtBlockId,
  last_used_block             =>l_last_used_block);


p('Total Blocks' , l_total_blocks);
p('Total Bytes' , l_total_bytes);
p('Unused Blocks' , l_unused_blocks);
p('Unused Bytes' , l_unused_bytes);
p('Last Used Ext FileId' , l_LastUsedExtFileId);
p('Last Used Ext BlockId' , l_LastUsedExtBlockId);
p('Last Used Block' , l_last_used_block);
end;

 

declare 
begin
  p_show_space('T_TEST');
end;

HWM= 8705408-6414=8698994

 

3. 删除数据，再次查看block情况

DELETE FROM T_TEST;
COMMIT;

--获取统计信息
declare 
begin
  DBMS_STATS.gather_table_stats('AA', 'T_TEST',degree => 3 , cascade => FALSE);
end;

--查看block使用情况
declare 
begin
  p_show_space('T_TEST');
end;

3. 使用alter table move操作

ALTER TABLE T_TEST MOVE;

--获取统计信息
declare 
begin
  DBMS_STATS.gather_table_stats('AA', 'T_TEST',degree => 3 , cascade => FALSE);
end;

--查看block使用情况
declare 
begin
  p_show_space('T_TEST');
end;

  再次查询block使用情况

  

HWM= 8 - 5 = 3

4.重建索引 rebulid

ALTER INDEX IDX1_ORDER_ID REBULID;