释放表的高水位通常有如下几种办法：
（1）对表进行MOVE操作：ALTER TABLE TABLE_NAME MOVE; 若表上存在索引，则记得重建索引。
（2）对表进行SHRINK SPACE操作：ALTER TABLE TABLE_NAME SHRINK SPACE;
注意，在执行该指令之前必须开启行移动：ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;
该方法的优点是：在碎片整理结束后，表上相关的索引仍然有效，缺点是会产生大量的UNDO和REDO。
（3）复制要保留的数据到临时表T，DROP原表，然后RENAME临时表T为原表。
（4）exp/imp或expdp/impdp重构表。
（5）若表中没有数据则直接使用TRUNCATE来释放高水位。
实验：
create table xmc.test1 as select * from dba_objects;
insert into xmc.test1 select * from xmc.test1;
create table xmc.test2 as select * from xmc.test1;
create index xmc.ind_test1_1 on xmc.test1(owner);
create index xmc.ind_test1_2 on xmc.test1(object_id);
收集统计信息
exec dbms_stats.gather_table_stats(ownname=>'XMC',tabname=>'TEST1',estimate_percent=>10,method_opt=>'for all indexed columns',cascade=>TRUE);
删除表
SQL> delete from XMC.TEST1;
收集统计信息
exec dbms_stats.gather_table_stats(ownname=>'XMC',tabname=>'TEST1',estimate_percent=>10,method_opt=>'for all indexed columns',cascade=>TRUE);
查询高水位，脚本2
OWNER TABLE_NAME SEGMENT_TYPE WASTE_PER
------------------------------ ------------------------------ ------------------ ----------
XMC TEST1 TABLE 99.88
APEX_030200 WWV_FLOW_DICTIONARY$ TABLE 99.41
XDB XDB$H_INDEX TABLE 99.22
方式1：move
SQL> alter table XMC.TEST1 MOVE;
OWNER TABLE_NAME SEGMENT_TYPE WASTE_PER
------------------------------ ------------------------------ ------------------ ----------
APEX_030200 WWV_FLOW_DICTIONARY$ TABLE 99.41
XDB XDB$H_INDEX TABLE 99.22
可以看到此表已经没有碎片了，记得重建索引
create index xmc.ind_test1_1 on xmc.test1(owner);
或
alter index xmc.ind_test1_1 rebuild online
收集统计信息，不然用脚本1查出来的还是没有释放碎片
exec dbms_stats.gather_table_stats(ownname=>'XMC',tabname=>'TEST1',estimate_percent=>10,method_opt=>'for all indexed columns',cascade=>TRUE);
方式2：shrink space
SQL> select count(*) from XMC.TEST2;
COUNT(*)
----------
172586
SQL> delete from XMC.TEST2;
172586 rows deleted.
SQL> COMMIT;
Commit complete.
收集统计信息：
exec dbms_stats.gather_table_stats(ownname=>'XMC',tabname=>'TEST2',estimate_percent=>10,method_opt=>'for all indexed columns',cascade=>TRUE);
脚本2查看
OWNER TABLE_NAME SEGMENT_TYPE WASTE_PER
------------------------------ ------------------------------ ------------------ ----------
XMC TEST1 TABLE 99.88
APEX_030200 WWV_FLOW_DICTIONARY$ TABLE 99.41
XDB XDB$H_INDEX TABLE 99.22
开始执行
SQL> ALTER TABLE XMC.TEST2 ENABLE ROW MOVEMENT;
Table altered.
SQL> alter table XMC.TEST2 SHRINK SPACE;
Table altered.
SQL> ALTER TABLE XMC.TEST2 DISABLE ROW MOVEMENT;
此时脚本2查看
OWNER TABLE_NAME SEGMENT_TYP WASTE_PER
------------------------------ --------------------------------------------- ------------------ ----------
APEX_030200 WWV_FLOW_DICTIONARY$ TABLE 99.41
XDB XDB$H_INDEX TABLE 99.22
脚本1查看
OWNER ROUND(D.NUM_ROWS/D.BLOCKS,2) NUM_ROWS BLOCKS TABLE_NAME T_SIZE
------------------------------ ---------------------------- ---------- ---------- ------------------------------ ----------
XDB .89 31 35 XDB$H_INDEX 0
APEX_030200 1.1 64 58 WWV_FLOW_TEMPLATES 0
XMC 0 0 2513 TEST2 20
收集统计信息：
exec dbms_stats.gather_table_stats(ownname=>'XMC',tabname=>'TEST2',estimate_percent=>10,method_opt=>'for all indexed columns',cascade=>TRUE);
脚本1查看：
OWNER ROUND(D.NUM_ROWS/D.BLOCKS,2) NUM_ROWS BLOCKS TABLE_NAME T_SIZE
------------------------------ ---------------------------- ---------- ---------- ------------------------------ ----------
XDB .89 31 35 XDB$H_INDEX 0
APEX_030200 1.1 64 58 WWV_FLOW_TEMPLATES 0
附带：查询高水位的SQL,参考（http://blog.itpub.net/26736162/viewspace-2139546/）
脚本1：
SELECT D.OWNER,
ROUND(D.NUM_ROWS / D.BLOCKS, 2),
D.NUM_ROWS,
D.BLOCKS,
D.TABLE_NAME,
ROUND((d.BLOCKS*8-D.INITIAL_EXTENT/1024)/1024) t_size
FROM DBA_TABLES D
WHERE D.BLOCKS > 10
AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5
AND d.OWNER NOT LIKE '%SYS%' ;
脚本2：
SELECT OWNER,
SEGMENT_NAME TABLE_NAME,
SEGMENT_TYPE,
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /
GREATEST(NVL(HWM, 1), 1)),
2),
0) WASTE_PER
FROM (SELECT A.OWNER OWNER,
A.SEGMENT_NAME,
A.SEGMENT_TYPE,
B.LAST_ANALYZED,
A.BYTES,
B.NUM_ROWS,
A.BLOCKS BLOCKS,
B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE(ROUND((B.AVG_ROW_LEN * NUM_ROWS *
(1 + (PCT_FREE / 100))) / C.BLOCKSIZE,
0),
0,
1,
ROUND((B.AVG_ROW_LEN * NUM_ROWS *
(1 + (PCT_FREE / 100))) / C.BLOCKSIZE,
0)) + 2 AVG_USED_BLOCKS,
ROUND(100 *
(NVL(B.CHAIN_CNT, 0) / GREATEST(NVL(B.NUM_ROWS, 1), 1)),
2) CHAIN_PER,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ C
WHERE A.OWNER = B.OWNER
AND SEGMENT_NAME = TABLE_NAME
AND SEGMENT_TYPE = 'TABLE'
AND B.TABLESPACE_NAME = C.NAME)
WHERE GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /
GREATEST(NVL(HWM, 1), 1)),
2),
0) > 50
AND OWNER NOT LIKE '%SYS%'
AND BLOCKS > 100
ORDER BY WASTE_PER DESC;
参考
1、降低表的高水位线
select 'alter table '||TABLE_NAME||' move tablespace '||TABLESPACE_NAME||';' from user_tables where table_name='&TABLE_NAME';
2、重建表上的索引
select 'alter index '||index_name||' rebuild online;' from user_indexes where table_name='&TABLE_NAME';
3、收集表上的统计信息
select 'analyze table '||TABLE_NAME||' compute statistics;' from user_tables where table_name='&TABLE_NAME';
4、收集索引上的统计信息
select 'analyze index '||index_name||' compute statistics;' from user_indexes where table_name='&TABLE_NAME';
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30654353/viewspace-2771641/ ，如需转载，请注明出处，否则将追究法律责任。
