Oracle10g中表的碎片空间回收

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

操作过程：
SQL> alter table T_test_data enable row movement; --支持行移动

Table altered.

SQL> set timing on
SQL> set serveroutput on
SQL> exec get_table_blocks('rocfy','T_test_data');
FS1 Blocks = 2826 Bytes =46301184
FS2 Blocks = 3996 Bytes =46301184
FS3 Blocks = 169 Bytes =46301184
FS4 Blocks = 7028 Bytes =46301184
Full Blocks = 179884 Bytes =2947219456
Unformatted Blocks= 240 Bytes =3932160

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.34

[@more@]

SQL> alter table T_test_data shrink space compact; --重组该表中现有的行

Table altered.

Elapsed: 00:02:15.81
SQL> exec get_table_blocks('rocfy','T_test_data');
FS1 Blocks = 19 Bytes =311296
FS2 Blocks = 11 Bytes =311296
FS3 Blocks = 9 Bytes =311296
FS4 Blocks = 8955 Bytes =311296
Full Blocks = 184909 Bytes =3029549056
Unformatted Blocks= 240 Bytes =3932160

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18
SQL> alter table T_test_data shrink space;--回收空间

Table altered.

Elapsed: 00:00:04.67
SQL> exec get_table_blocks('rocfy','T_test_data');
FS1 Blocks = 18 Bytes =294912
FS2 Blocks = 10 Bytes =294912
FS3 Blocks = 8 Bytes =294912
FS4 Blocks = 6 Bytes =294912
Full Blocks = 184911 Bytes =3029581824
Unformatted Blocks= 0 Bytes =0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16
SQL> alter table T_test_data shrink space cascade; --也可以用一条语句来压缩表的索引

Table altered.

Elapsed: 00:00:58.92
SQL> exec get_table_blocks('rocfy','T_test_data');
FS1 Blocks = 18 Bytes =294912
FS2 Blocks = 10 Bytes =294912
FS3 Blocks = 8 Bytes =294912
FS4 Blocks = 6 Bytes =294912
Full Blocks = 184911 Bytes =3029581824
Unformatted Blocks= 0 Bytes =0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18
SQL>
可以对比看出回收的BLOCK数

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

转载于:http://blog.itpub.net/42810/viewspace-983344/