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

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数