Oracle中space什么意思,Oracle中shrink space命令详解

3 删除后的信息

然后我们随机地从table MY_OBJECTS中删除一部分数据：

SQL> delete from my_objects where object_name like '%C%';

17674 rows deleted

SQL> delete from my_objects where object_name like '%U%';

4687 rows deleted

SQL> delete from my_objects where object_name like '%A%';

7010 rows deleted

SQL> exec show_space('MY_OBJECTS');

Total Blocks  ..........................768

Total Bytes   ..........................6291456

Total MBytes  ..........................6

Unused Blocks ..........................62

Unused Bytes  ..........................507904

Unused KBytes ..........................496

Last Used Ext FileId....................7

Last Used Ext BlockId...................649

Last Used Block.........................66

The segment is analyzed below

FS1 Blocks (0-25)   ....................0

FS2 Blocks (25-50)  ....................212

FS3 Blocks (50-75)  ....................181

FS4 Blocks (75-100) ....................245

Unformatted Blocks  ....................0

Full Blocks         ....................48

PL/SQL procedure successfully completed

这里，table my_objects的HWM下有706(768 - 62)个block，其中，free space为25-50%的block有205个，free space为50-75%的block有180个，free space为75-100%的block有229个，full space的block只有45个，这种情况下，我们需要对这个table的现有数据行进行重组。

4 shink操作

要使用assm上的shink，首先我们需要使该表支持行移动，可以用这样的命令来完成：

SQL> alter table my_objects enable row movement;

Table altered

现在，就可以来降低my_objects的HWM，回收空间了，使用命令：

SQL> alter table my_objects shrink space;

Table altered

SQL> exec show_space('MY_OBJECTS');

Total Blocks  ..........................280

Total Bytes   ..........................2293760

Total MBytes  ..........................2.1875

Unused Blocks ..........................5

Unused Bytes  ..........................40960

Unused KBytes ..........................40

Last Used Ext FileId....................7

Last Used Ext BlockId...................265

Last Used Block.........................19

The segment is analyzed below

FS1 Blocks (0-25)   ....................0

FS2 Blocks (25-50)  ....................1

FS3 Blocks (50-75)  ....................1

FS4 Blocks (75-100) ....................0

Unformatted Blocks  ....................0

Full Blocks         ....................259

PL/SQL procedure successfully completed

在执行玩shrink命令后，此时表my_objects的HWM现在降到了276(280 - 5 + 1)的位置，而且HWM下的block的空间使用状况，full space的block有259个，free space 为25-50% 和50-75% Block只有1个。