高水位表检查以及回收高水位

 1) 高水位表收集 SELECT NUM_ROWS,AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9 NEED, BLOCKS*8/1024 TRUE,(BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9) RECOVER_MB,TABLE_NAME FROM dba_tables WHERE tablespace_name='表空间名' AND BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9>100 AND rownum<11 order by RECOVER_MB desc; 统计信息的脚本：exec dbms_stats.gather_table_stats('user','table_name'); 2) 启用表的行转移功能 SQL> alter table  '表名'   enable row movement ; 3) 压缩高水位线 SQL> alter table '表名'  shrink space ; 4) 查看统计信息 SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS   from user_tables t  where table_name = upper('表名'); 5) 收集统计信息 SQL> exec dbms_stats.gather_table_stats('sys','表名'); 6) 再次查看 SQL> select t.table_name,BLOCKS,EMPTY_BLOCKS,NUM_ROWS   from dba_tables t  where table_name = upper('表名');