如何降低Oracle表的高水位?
什麼是oracle高水位:oracle表的高水位可以了解為表的資料量曾經到達某個點,由于後來資料的删除,現實的資料并沒有達到這個點,并且有可能遠遠低于這個點。
oracle高水位的英文就是:High Water Mark.
産生高水位的原因有二:一是表有大量的delete操作,最常見的就是oracle物化視圖日志;另一種就是用了insert 。注意:insert復原時,高水位是不會復原的。
降低表的高水位,oracle提供了幾種常見的方法:
1.對表進行MOVE,做完MOVE後需要對表的所有過引進行重建(注意MOVE時需要雙倍的表空間)。
參考腳本:
view plain copy to clipboard print ?
- 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,然後回寫:
view plain copy to clipboard print ?
- 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;
view plain copy to clipboard print ?
- 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降低高水位:
[email protected]>create table yy as
2 select *
3 from dba_tables dt;
[email protected]>
[email protected]>analyze TABLE yy compute statistics;
[email protected]>
[email protected]>
[email protected]>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
[email protected]>
[email protected]>SELECT T.TABLE_NAME,T.BLOCKS FROM USER_TABLES T
2 WHERE T.TABLE_NAME='YY';
TABLE_NAME BLOCKS
------------------------------ ----------
YY 60
[email protected]>
[email protected]>DELETE YY
2 WHERE ROWNUM <
3 (SELECT COUNT(1)-10 FROM YY);
[email protected]>analyze TABLE yy compute statistics;
[email protected]>
[email protected]>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
[email protected]>
[email protected]>SELECT T.TABLE_NAME,T.BLOCKS FROM USER_TABLES T
2 WHERE T.TABLE_NAME='YY';
TABLE_NAME BLOCKS
------------------------------ ----------
YY 60
[email protected]>
[email protected]>ALTER TABLE YY MOVE;
[email protected]>
[email protected]>
[email protected]>analyze TABLE yy compute statistics;
[email protected]>
[email protected]>
[email protected]>select UE.BYTES,UE.BLOCKS
2 from user_extents ue
3 where ue.segment_name='YY';
BYTES BLOCKS
---------- ----------
65536 8
[email protected]>
[email protected]>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