介绍两种方法来降低HWM

方法一：通过TRUNCATE表来降低高水位

SQL> create tablespace test datafile '/u01/app/oradata/orcl/test01.dbf' size 10m autoextend on;

SQL> create table test(num number) tablespace test;

SQL> select BLOCKS,EXTENTS from dba_segments where SEGMENT_NAME='TEST';

BLOCKS EXTENTS

---------- ----------

8 1

SQL> select BLOCKS,EMPTY_BLOCKS,NUM_ROWS from dba_tables where table_name='TEST';

BLOCKS EMPTY_BLOCKS NUM_ROWS

---------- ------------ ----------

0 8 0
注释：创建新表TEST后，系统自动分配了一个区即8个块，当前块均是空块未格式化

SQL> declare

2 begin

3 for i in 1..10000 loop

4 insert into test values(i);

5 end loop;

6 commit;

7 end;

8 /

SQL> analyze table test estimate statistics;

SQL> select BLOCKS,EMPTY_BLOCKS,NUM_ROWS from dba_tables where table_name='TEST';

BLOCKS EMPTY_BLOCKS NUM_ROWS

---------- ------------ ----------

20 4 10000
注释：向TEST表插入数据后，系统格式化了20个块用于数据插入，剩余4个空块

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from TEST;

used blocks

-----------

16
注释：当前实际16个数据块真实包含数据

SQL> truncate table test reuse storage;
注释：执行DDL操作

SQL> analyze table test estimate statistics;

SQL> select BLOCKS,EMPTY_BLOCKS,NUM_ROWS from dba_tables where table_name='TEST';

BLOCKS EMPTY_BLOCKS NUM_ROWS

---------- ------------ ----------

0 8 0
注释：均是空块

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from TEST;

used blocks

-----------

0

方法一：通过SHRINK表来降低高水位

SQL> create tablespace test datafile '/u01/app/oradata/orcl/test01.dbf' size 10m autoextend on;

SQL> create table test(num number) tablespace test;

SQL> select BLOCKS,EXTENTS from dba_segments where SEGMENT_NAME='TEST';

BLOCKS EXTENTS

---------- ----------

8 1

SQL> select BLOCKS,EMPTY_BLOCKS,NUM_ROWS from dba_tables where table_name='TEST';

BLOCKS EMPTY_BLOCKS NUM_ROWS

---------- ------------ ----------

0 8 0

SQL> declare

2 begin

3 for i in 1..10000 loop

4 insert into test values(i);

5 end loop;

6 commit;

7 end;

8 /

SQL> analyze table test estimate statistics;

SQL> select BLOCKS,EMPTY_BLOCKS,NUM_ROWS from dba_tables where table_name='TEST';

BLOCKS EMPTY_BLOCKS NUM_ROWS

---------- ------------ ----------

20 4 10000

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from TEST;

used blocks

-----------

16

SQL> delete from test;

SQL> commit;

SQL> select BLOCKS,EMPTY_BLOCKS,NUM_ROWS from dba_tables where table_name='TEST';

BLOCKS EMPTY_BLOCKS NUM_ROWS

---------- ------------ ----------

20 4 0

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from TEST;

used blocks

-----------

0

SQL> alter table test enable ROW MOVEMENT;

SQL> alter table test shrink space;
注释：执行shrink操作

SQL> analyze table test estimate statistics;

SQL> select BLOCKS,EMPTY_BLOCKS,NUM_ROWS from dba_tables where table_name='TEST';

BLOCKS EMPTY_BLOCKS NUM_ROWS

---------- ------------ ----------

1 7 0
注释：经过对比，确实降低了HWM

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from TEST;

used blocks

-----------

0

附：

dbms_rowid.rowid_relative_fno：定位该数据行所在的数据文件

dbms_rowid.rowid_block_number：定位该数据行在数据文件的第多少个块

dbms_rowid.rowid_row_number：定位该数据行在数据块的第多少行

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from TEST;

used blocks

-----------

0
注释：适用场景segment用了一个file

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)||'-'||dbms_rowid.rowid_relative_fno (rowid)) "used blocks" from TEST;

used blocks

-----------

0
注释：适用场景segment用了多个file