Oracle10g shrink提供了下面的几个功能:
1)alter table tbname shrink space; 回缩表与HWM
2)alter table tbname shrink space compact; 回缩表,保持HWM
3)alter table tbname shrink space cascade; 回缩表与相关索引
4)alter index idxname shrink space; 回缩索引
SQL> var space_usage varchar2(1000);
SQL> exec p_space_usage('SCOTT','T_SHRINK',:SPACE_USAGE);
PL/SQL procedure successfully completed
space_usage
---------
TOTAL_BLOCKS = 12288
TOTAL_BYTES = 100663296
UNUSED_BLOCKS = 512
UNUSED_BYTES = 4194304
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 11401
LAST_USED_BLOCK = 512
SQL> delete from t_shrink where rownum<100000;
99999 rows deleted
SQL> commit;
Commit complete
SQL> exec p_space_usage('SCOTT','T_SHRINK',:SPACE_USAGE);
PL/SQL procedure successfully completed
space_usage
---------
TOTAL_BLOCKS = 12288
TOTAL_BYTES = 100663296
UNUSED_BLOCKS = 512
UNUSED_BYTES = 4194304
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 11401
LAST_USED_BLOCK = 512
可以看到DELETE操作后HWM并没有降低
SQL> alter table t_shrink shrink space compact;
alter table t_shrink shrink space compact
ORA-10636: ROW MOVEMENT is not enabled
SQL> alter table t_shrink enable row movement;
Table altered
进行SHRINK之前必须先启动行移动。
SQL> alter table t_shrink shrink space compact;
Table altered
SQL> exec p_space_usage('SCOTT','T_SHRINK',:SPACE_USAGE);
PL/SQL procedure successfully completed
space_usage
---------
TOTAL_BLOCKS = 12288
TOTAL_BYTES = 100663296
UNUSED_BLOCKS = 512
UNUSED_BYTES = 4194304
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 11401
LAST_USED_BLOCK = 512
使用shrink space compact时,ORACLE会回收表的空间,但不会降低HWM。
SQL> alter table t_shrink shrink space;
Table altered
SQL> exec p_space_usage('SCOTT','T_SHRINK',:SPACE_USAGE);
PL/SQL procedure successfully completed
space_usage
---------
TOTAL_BLOCKS = 8632
TOTAL_BYTES = 70713344
UNUSED_BLOCKS = 5
UNUSED_BYTES = 40960
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 8329
LAST_USED_BLOCK = 435
SQL> truncate table t_shrink;
Table truncated
SQL> exec p_space_usage('SCOTT','T_SHRINK',:SPACE_USAGE);
PL/SQL procedure successfully completed
space_usage
---------
TOTAL_BLOCKS = 8
TOTAL_BYTES = 65536
UNUSED_BLOCKS = 5
UNUSED_BYTES = 40960
LAST_USED_EXTENT_FILE_ID = 4
LAST_USED_EXTENT_BLOCK_ID = 97
LAST_USED_BLOCK = 3
Shrink功能支持下面的对象:
1) 普通表和索引
2) 物理View
3) 物理View log
4) 大对象
5) IOT(索引组织表)
而对于shrink功能也有一些限制:
1) cluster中的表
2) 有long类型的表
3) 有on_commit物化视图的表
4) 有基于rowid物化视图的表
5) 大对象(LOB)索引
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25801738/viewspace-714457/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25801738/viewspace-714457/