ITPub博客

首页 > 数据库 > Oracle > oracle shrink

oracle shrink

原创 Oracle 作者:宋祖强 时间:2016-01-19 17:03:04 0 删除 编辑

从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’ ;    www.2cto.com  
 
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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29289867/viewspace-1979140/,如需转载,请注明出处,否则将追究法律责任。

上一篇: dbms_rowid的使用
请登录后发表评论 登录
全部评论

注册时间:2014-08-13

  • 博文量
    176
  • 访问量
    276789