表空间收缩

  在删除了大量数据之后,需要进行表空间的收缩,否则,原先数据所占用的空间不会释放.通过查询可知占用空间。

 

SELECT SUM(BYTES)/1024/1024 FROM DBA_EXTENTS WHERE SEGMENT_NAME='TABLE_NAME';

SELECT SUM(BYTES)/1024/1024 FROM DBA_EXTENTS WHERE SEGMENT_NAME='INDEX_NAME';

 

以前用过一个办法：

先将表TRNNCATE，再重新insert，就可以实现表空间的回收。

对于应用不需要实时更新，可以采用。

 

如果需要在线更新：

 

ORACLE 提供shrink方式：

 

shrink_clause

The shrink clause lets you manually shrink space in a table, index-organized table or its overflow segment, index, partition, subpartition, LOB segment, materialized view, or materialized view log. This clause is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space immediately.

Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.

COMPACT If you specify COMPACT, then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE ... SHRINK SPACE statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step.

For an index or index-organized table, specifying ALTER [INDEX | TABLE] ... SHRINK SPACE COMPACT is equivalent to specifying ALTER [INDEX | TABLE ... COALESCE. The shrink_clause can be cascaded (please refer to the CASCADE clause, which follows) and compacts the segment more densely than does a coalesce operation, which can improve performance. However, if you do not want to release the unused space, then you can use the appropriate COALESCE clause.

CASCADE If you specify CASCADE, then Oracle Database performs the same operations on all dependent objects of table, including secondary indexes on index-organized tables.

 

 

 

虽然alter table move和shrink space，都是通过物理调整rowid来整理碎片的，但shrink space整理的不彻底，他好像不是重组，而是尽可能的合并，随意会残留一些block无法整理

　　注意：

　　1.再用alter table table_name move时，表相关的索引会失效，所以之后还要执行 alter index index_name rebuild online; 最后重新编译数据库所有失效的对象

　　2. 在用alter table table_name shrink space cascade时，他相当于alter table table_name move和alter index index_name rebuild online. 所以只要编译数据库失效的对象就可以

　　alter table move和shrink space除了碎片整理的效果有时不一样外，还有什么其他的不同呢

　　1. Move会移动高水位，但不会释放申请的空间，是在高水位以下(below HWM)的操作。

　　2. shrink space 同样会移动高水位，但也会释放申请的空间，是在高水位上下(below and above HWM)都有的操作。