如何降低Oracle表的高水位？

wjlcn 发表于 2011-12-22 08:54

<a href="http://www.idb-stock.net/idb/2011/05/16/107.html" rel="bookmark" title="Permanent Link to 如何降低Oracle表的高水位？" target="_blank">如何降低Oracle表的高水位？</a> 什么是oracle高水位：oracle表的高水位可以理解为表的数据量曾经到达某个点，由于后来数据的删除，现实的数据并没有达到这个点，并且有可能远远低于这个点。 
oracle高水位的英文就是:High Water Mark. 
产生高水位的原因有二：一是表有大量的delete操作，最常见的就是oracle物化视图日志；另一种就是用了insert /*+append*/。注意：insert/*+append*/回滚时，高水位是不会回滚的。
降低表的高水位，oracle提供了几种常见的方法： 
1.对表进行MOVE，做完MOVE后需要对表的所有过引进行重建（注意MOVE时需要双倍的表空间）。 
参考脚本：
<div class="dp-highlighter"><div class="bar"><div class="tools"><a href="http://www.idb-stock.net/idb/2011/05/16/107.html#" target="_blank">view plain</a><a href="http://www.idb-stock.net/idb/2011/05/16/107.html#" target="_blank">copy to clipboard</a><a href="http://www.idb-stock.net/idb/2011/05/16/107.html#" target="_blank">print</a><a href="http://www.idb-stock.net/idb/2011/05/16/107.html#" target="_blank">?</a></div></div><ol class="dp-sql" start="1"><li class="alt">alter table table_name move tablespace tbs_name;  </li><li class="">select 'alter index '||index_name||' rebuild;' sql_text  </li><li class="alt">from user_index ui  </li><li class="">where ui.table_name='&tab_name';  </li></ol></div>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，然后回写：
<div class="dp-highlighter"><div class="bar"><div class="tools"><a href="http://www.idb-stock.net/idb/2011/05/16/107.html#" target="_blank">view plain</a><a href="http://www.idb-stock.net/idb/2011/05/16/107.html#" target="_blank">copy to clipboard</a><a href="http://www.idb-stock.net/idb/2011/05/16/107.html#" target="_blank">print</a><a href="http://www.idb-stock.net/idb/2011/05/16/107.html#" target="_blank">?</a></div></div><ol class="dp-sql" start="1"><li class="alt">create table bak_table_name as select * from table_name;  </li><li class="">truncate table table_name;  </li><li class="alt">insert into table_name select * from bak_table_name;  </li><li class="">commit;  </li></ol></div>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;
<div class="dp-highlighter"><div class="bar"><div class="tools"><a href="http://www.idb-stock.net/idb/2011/05/16/107.html#" target="_blank">view plain</a><a href="http://www.idb-stock.net/idb/2011/05/16/107.html#" target="_blank">copy to clipboard</a><a href="http://www.idb-stock.net/idb/2011/05/16/107.html#" target="_blank">print</a><a href="http://www.idb-stock.net/idb/2011/05/16/107.html#" target="_blank">?</a></div></div><ol class="dp-sql" start="1"><li class="alt">alter table table_name enable row movement;  </li><li class="">alter table table_name shring space;  </li></ol></div>alter table table_name enable row movement;
alter table table_name shring space;

用alter table move降低高水位：
dw@dw>create table yy as 2select * 3from dba_tables dt; dw@dw> dw@dw>analyze TABLE yy compute statistics; dw@dw> dw@dw> dw@dw>select UE.BYTES,UE.BLOCKS 2from user_extents ue 3where 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 2WHERE T.TABLE_NAME='YY'; TABLE_NAME BLOCKS ------------------------------ ---------- YY 60 dw@dw> dw@dw>DELETE YY 2WHERE ROWNUM < 3(SELECT COUNT(1)-10 FROM YY); dw@dw>analyze TABLE yy compute statistics; dw@dw> dw@dw>select UE.BYTES,UE.BLOCKS 2from user_extents ue 3where 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 2WHERE 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 2from user_extents ue 3where ue.segment_name='YY'; BYTES BLOCKS ---------- ---------- 65536 8 dw@dw> dw@dw>SELECT T.TABLE_NAME,T.BLOCKS FROM USER_TABLES T 2WHERE T.TABLE_NAME='YY'; TABLE_NAME BLOCKS ------------------------------ ---------- YY 4 转载请注明：本文来自iDB Stock:<a href="http://www.idb-stock.net/idb/2011/05/16/107.html" target="_blank">http://www.idb-stock.net/idb/2011/05/16/107.html</a>

页: [1]

Chinaunix's Archiver

如何降低Oracle表的高水位？