如何降低Oracle表的高水位?
什么是oracle高水位:oracle表的高水位可以理解为表的数据量曾经到达某个点,由于后来数据的删除,现实的数据并没有达到这个点,并且有可能远远低于这个点。
oracle高水位的英文就是:High Water Mark.
产生高水位的原因有二:一是表有大量的delete操作,最常见的就是oracle物化视图日志;另一种就是用了insert /*+append*/。注意:insert/*+append*/回滚时,高水位是不会回滚的。
降低表的高水位,oracle提供了几种常见的方法:
1.对表进行MOVE,做完MOVE后需要对表的所有过引进行重建(注意MOVE时需要双倍的表空间)。
参考脚本:
- alter table table_name move tablespace tbs_name;
- select 'alter index '||index_name||' rebuild;' sql_text
- from user_index ui
- where ui.table_name='&tab_name';
alter table table_name move tablespace tbs_name;
select 'alter index '||index_name||' rebuild;' sql_text
from user_index ui
where ui.table_name='&tab_name';
2.以ctas创建备份表,将源表truncate,然后回写:
- create table bak_table_name as select * from table_name;
- truncate table table_name;
- insert into table_name select * from bak_table_name;
- commit;
create table bak_table_name as select * from table_name;
truncate table table_name;
insert into table_name select * from bak_table_name;
commit;
3.方法1、2对于小表比较适合,如果对上G的表进行操作,可能就比较麻烦了。建议进行exp/imp操作。
4.对于Oracle 10g可以采用alter table shrink space;
- alter table table_name enable row movement;
- alter table table_name shring space;
alter table table_name enable row movement;
alter table table_name shring space;
用alter table move降低高水位:
dw@dw>create table yy as 2 select * 3 from dba_tables dt; dw@dw> dw@dw>analyze TABLE yy compute statistics; dw@dw> dw@dw> dw@dw>select UE.BYTES,UE.BLOCKS 2 from user_extents ue 3 where ue.segment_name='YY'; BYTES BLOCKS ---------- ---------- 65536 8 65536 8 65536 8 65536 8 65536 8 65536 8 65536 8 65536 8 dw@dw> dw@dw>SELECT T.TABLE_NAME,T.BLOCKS FROM USER_TABLES T 2 WHERE T.TABLE_NAME='YY'; TABLE_NAME BLOCKS ------------------------------ ---------- YY 60 dw@dw> dw@dw>DELETE YY 2 WHERE ROWNUM < 3 (SELECT COUNT(1)-10 FROM YY); dw@dw>analyze TABLE yy compute statistics; dw@dw> dw@dw>select UE.BYTES,UE.BLOCKS 2 from user_extents ue 3 where ue.segment_name='YY'; BYTES BLOCKS ---------- ---------- 65536 8 65536 8 65536 8 65536 8 65536 8 65536 8 65536 8 65536 8 dw@dw> dw@dw>SELECT T.TABLE_NAME,T.BLOCKS FROM USER_TABLES T 2 WHERE T.TABLE_NAME='YY'; TABLE_NAME BLOCKS ------------------------------ ---------- YY 60 dw@dw> dw@dw>ALTER TABLE YY MOVE; dw@dw> dw@dw> dw@dw>analyze TABLE yy compute statistics; dw@dw> dw@dw> dw@dw>select UE.BYTES,UE.BLOCKS 2 from user_extents ue 3 where ue.segment_name='YY'; BYTES BLOCKS ---------- ---------- 65536 8 dw@dw> dw@dw>SELECT T.TABLE_NAME,T.BLOCKS FROM USER_TABLES T 2 WHERE T.TABLE_NAME='YY'; TABLE_NAME BLOCKS ------------------------------ ---------- YY 4
转载请注明:本文来自iDB Stock:http://www.idb-stock.net/idb/2011/05/16/107.html