Oracle10g中表的碎片空间回收

1、Oracle10g 中表的碎片空间回收Oracle10g 中表的碎片空间回收对于一些对表经常操作的数据来说。大量的增加及删除操作 会导致表的占用空间浪费，下面的操作可以回收整理。减少 不必要的空间占用。需要先创建一个存储过程来计算表或者索引占用的空间及 使用的空闲空间。存储过程名：get_table_blocks(rocfy,T_test_data); 传入参数为当前用户 和对应要计算的表名或者索引名称即可。具体存储过程代码在后面在复制上来。操作过程：SQL alter table T_test_data enable row movement; -支持行移动Table altered.SQL

2、 set timing onSQL set serveroutput onSQL exec get_table_blocks(rocfy,T_test_data);FS1 Blocks = 2826 Bytes =46301184FS2 Blocks = 3996 Bytes =46301184FS3 Blocks = 169 Bytes =46301184FS4 Blocks = 7028 Bytes =46301184Full Blocks = 179884 Bytes =2947219456 Unformatted Blocks= 240 Bytes =3932160 PL/SQL pr

3、ocedure successfully completed. Elapsed: 00:00:00.34SQL alter table T_test_data shrink space compact; - 重组 该表中现有的行Table altered.Elapsed: 00:02:15.81SQL exec get_table_blocks(rocfy,T_test_data);FS1 Blocks = 19 Bytes =311296FS2 Blocks = 11 Bytes =311296FS3 Blocks = 9 Bytes =311296FS4 Blocks = 8955 Byt

4、es =311296Full Blocks = 184909 Bytes =3029549056 Unformatted Blocks= 240 Bytes =3932160PL/SQL procedure successfully completed. Elapsed: 00:00:00.18SQL alter table T_test_data shrink space;-回收空间Table altered.Elapsed: 00:00:04.67SQL exec get_table_blocks(rocfy,T_test_data);FS1 Blocks = 18 Bytes =2949

5、12FS2 Blocks = 10 Bytes =294912FS3 Blocks = 8 Bytes =294912FS4 Blocks = 6 Bytes =294912Full Blocks = 184911 Bytes =3029581824Unformatted Blocks= 0 Bytes =0PL/SQL procedure successfully completed.Elapsed: 00:00:00.16也可SQL alter table T_test_data shrink space cascade; - 以用一条语句来压缩表的索引Table altered.Elapsed: 00:00:58.92SQL exec get_table_blocks(rocfy,T_test_data);FS1 Blocks = 18 Bytes =294912FS2 Blocks = 10 Bytes =294912FS3 Blocks = 8 Bytes =294912FS4 Blocks = 6 Bytes =294912Full Blocks = 184911 Bytes =3029581824Unformatted Blocks=