一个shrink实践

这只是看一下shrink的效果、所需时间
而且对show_space的输出的效果影响
以备对以后几个大表（3G,7G等）的收缩做一个准备
这里并没有对内部机制进行讨论
有兴趣的朋友可以看
http://blog.csdn.net/tianlesoftware/archive/2009/11/03/4764254.aspx
另：
1.因为操作太快，这次并没有对shrink产生的锁进行探讨
2.如果了解show_space脚本可以看
http://guoyanxi.iteye.com/blog/550299

--查询表现在所占空间
SET line 90;
col segment_name format a35;
SELECT segment_name,BYTEs / 1024 / 1024
  FROM dba_segments
 WHERE segment_name ='WMS_BA_MQ_RECEIVE_MES1';
--结果
segment_name                        BYTES/1024/1024
----------------------------------- ---------------
WMS_BA_MQ_RECEIVE_MES1                          111

--show space一下
SET serveroutput ON;
EXEC Show_Space('WMS_BA_MQ_RECEIVE_MES1','auto','T','Y'); 
Total Blocks............................14208
Total Bytes.............................116391936
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................17
Last Used Ext BlockId...................867081
Last Used Block.........................128
 *************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............1
50% -- 75% free space bytes.............8192
75% -- 100% free space blocks...........13070
75% -- 100% free space bytes............107069440
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................985
Total bytes.............................8069120
PL/SQL procedure successfully completed.

--开始shrink
SET timing ON;
ALTER TABLE WMS_BA_MQ_RECEIVE_MES1 ENABLE ROW MOVEMENT;
Table altered.
Elapsed: 00:00:00:07
ALTER TABLE WMS_BA_MQ_RECEIVE_MES1 SHRINK SPACE;
Table altered.
Elapsed: 00:00:40:54
SET serveroutput ON;
EXEC Show_Space('WMS_BA_MQ_RECEIVE_MES1','auto','T','Y');
Total Blocks............................1024
Total Bytes.............................8388608
Unused Blocks...........................49
Unused Bytes............................401408
Last Used Ext FileId....................17
Last Used Ext BlockId...................321417
Last Used Block.........................79
 *************************************************
The segment is analyzed
0% -- 25% free space blocks.............1
0% -- 25% free space bytes..............8192
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........3
75% -- 100% free space bytes............24576
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................953
Total bytes.............................7806976
PL/SQL procedure successfully completed.
Elapsed: 00:00:01:09

--再查看使用空间
segment_name                        BYTES/1024/1024
----------------------------------- ---------------
WMS_BA_MQ_RECEIVE_MES1                            8
Elapsed: 00:00:01:09