oracle shrink keep,oracle shrink

从10g开始，oracle开始提供Shrink的命令，假如我们的表空间中支持自动段空间管理 (ASSM),就可以使用这个特性缩小段，即降低HWM。

segment shrink分为两个阶段：

1、数据重组(compact):通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改变，需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。

2、HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的上可能造成比较大的影响。

shrink space语句两个阶段都执行。

shrink space compact只执行第一个阶段。

如果系统业务比较繁忙，可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。

shrink必须开启行迁移功能。

alter table table_name enable row movement ;

注意：alter table XXX enable row movement语句会造成引用表XXX的对象(如存储过程、包、视图等)变为无效。执行完成后，最好执行一下utlrp.sql来编译无效的对象。

语法：

alter table shrink space [ | compact | cascade ];

alter table shrink space compcat;

收缩表，相当于把块中数据打结实了，但会保持 high water mark;

alter table shrink space;

收缩表，降低 high water mark;

alter table shrink space cascade;

收缩表，降低 high water mark，并且相关索引也要收缩一下下。

alter index idxname shrink space;

回缩索引

1：普通表

Sql脚本，改脚本会生成相应的语句

select’alter table ‘||table_name||’ enable row movement;’||chr(10)||’alter table ‘||table_name||’ shrink space;’||chr(10)from user_tables;

select’alter index ‘||index_name||’ shrink space;’||chr(10)from user_indexes;

2：分区表的处理

进行shrink space时 发生ORA-10631错误.shrink space有一些限制.

在表上建有函数索引(包括全文索引)会失败。

Sql脚本，改脚本会生成相应的语句

select ‘alter table ‘||table_name||’ enable row movement;’||chr(10)||’alter table ‘||table_name||’ shrink space;’||chr(10) from user_tables where ;

select ‘alter index ‘||index_name||’ shrink space;’||chr(10) from user_indexes where uniqueness=’NONUNIQUE’ ;

select ‘alter table ‘||segment_name||’ modify subpartition ‘||partition_name||’ shrink space;’||chr(10) from user_segments where segment_type=’TABLE SUBPARTITION’ ‘;

另外，对于频繁操作的表可以缓存到内存中

oracle的db_buffer_pool由三部分组成：

buffer_pool_defualt

buffer_pool_keep

buffer_pool_recycle

如果要把表钉死在内存中，也就是把表钉在keep区。

相关的命令为：

alter table ….. storage(buffer_pool keep);

这句命令把表示表如果缓存的话是缓存在keep区。

可以通过语句：

select table_name from dba_tables where buffer_pool=’KEEP’;查询到改表是放在keep区中的。

但是不意味着表已经被缓存了。

下面的语句把表缓存：

alter table …. cache;

可以通过

select table_name from dba_ tables where rtrim(cache)=’Y’

查询到该表已经被缓存了。

加入到keep区的表不是说不能被移出内存，不过是比较不容易移出内存。

也可以手工来移出内存，命令如下：

alter table … nocache;

Availability

============

Segment shrink is done online, thereby it increases the availability of the object.

While conventional DML operations can coexist with segment shrink, parallel DMLs

cannot.

During segment shrink, data will be moved as part of the compaction phase. During

compaction locks will be held on individual rows and/or blocks containing the data.

This will cause the concurrent DMLs like updates and deletes to serialize on the

locks.The compaction will be done in units of smaller transactions, so the availability

of the object will not be impacted significantly.

However during certain phases of segment shrink (when the HWM is adjusted), the segment

will have to be locked in exclusive mode.

This phase is for a very short duration and should impact the availability of the

object less significantly.

跟据上面的解释，shrink space操作可以随时kill或cancel也没有问题的，等有空了再放上去跑就是了，反正之前做过的都不是白做的

先：

alter table XXX shrink space compact;

然后在空闲的时候再：

alter table XXX shrink space;

需要压缩索引的话可以同时指定cascade选项

文档上的说法：

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.