这里展示一下使用表的SHRINK技术完成空间的释放。同时注意，在使用这种方法之前要注意启动行移动的特性（如果不启用，通过报错信息亦可以得到相应的提示信息）。

1.默认情况下表上的行移动是关闭的
如果不是关闭的状态，我们也可以使用下面命令完成关闭的动作。
sec@ora10g> alter table t disable row movement;

Table altered.

sec@ora10g> select table_name,blocks,empty_blocks,num_rows,row_movement from user_tables where table_name='T';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS ROW_MOVE
------------------------------ ---------- ------------ ---------- --------
T                                    4071            0     358277 DISABLED

2.增加表T中的数据量
sec@ora10g> insert into t select * from t;

357952 rows created.

sec@ora10g> commit;

Commit complete.

sec@ora10g> exec dbms_stats.gather_table_stats(ownname=>'SEC',tabname=> 'T');

PL/SQL procedure successfully completed.

sec@ora10g> select table_name,blocks,empty_blocks,num_rows,row_movement from user_tables where table_name='T';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS ROW_MOVE
------------------------------ ---------- ------------ ---------- --------
T                                    9131            0     721580 DISABLED

此时可见数据量为721580，占用9131个数据库块。

3.清空表T中的内容
sec@ora10g> delete from t;

715904 rows deleted.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select count(*) from t;

COUNT(*)
----------
         0

4.对表T进行分析，获得清空后表的信息
sec@ora10g> exec dbms_stats.gather_table_stats(ownname=>'SEC',tabname=> 'T');

PL/SQL procedure successfully completed.

sec@ora10g> select table_name,blocks,empty_blocks,num_rows,row_movement from user_tables where table_name='T';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS ROW_MOVE
------------------------------ ---------- ------------ ---------- --------
T                                    9131            0          0 DISABLED

可见，此时表T的记录条数已经显示为0，但所占用的数据库块数没有改变。

5.在表T上尝试shrink操作
sec@ora10g> alter table t shrink space;
alter table t shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

提示信息很显然，需要我们开启行移动后方可。

6.启动表T上的行移动功能
sec@ora10g> alter table t enable row movement;

Table altered.

sec@ora10g> select table_name,blocks,empty_blocks,num_rows,row_movement from user_tables where table_name='T';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS ROW_MOVE
------------------------------ ---------- ------------ ---------- --------
T                                    9131            0          0 ENABLED

此时ROW_MOVE字段已经显示表T上的行移动功能已经开启。

7.再次完成表T的shrink操作
sec@ora10g> alter table t shrink space;

Table altered.

sec@ora10g> exec dbms_stats.gather_table_stats(ownname=>'SEC',tabname=> 'T');

PL/SQL procedure successfully completed.

sec@ora10g> select table_name,blocks,empty_blocks,num_rows,row_movement from user_tables where table_name='T';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS   NUM_ROWS ROW_MOVE
------------------------------ ---------- ------------ ---------- --------
T                                       1            0          0 ENABLED

OK，目的达到。目前使用到的数据库块的数据已经降低到1。

8.有关表的空间释放shrink方法的更多参考信息
参考Oracle官方文档中的“shrink_clause”部分的描述。
该部分对shrink的语法和注意事项有很全面的描述，摘录在此，供参考。

shrink_clause

The shrink clause lets you manually shrink space in a table, index-organized table or its overflow segment, index, partition, subpartition, LOB segment, materialized view, or materialized view log. This clause is valid only for segments in tablespaces with automatic segment management. By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space immediately.

Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.

COMPACT If you specify COMPACT, then Oracle Database only defragments the segment space and compacts the table rows for subsequent release. The database does not readjust the high water mark and does not release the space immediately. You must issue another ALTER TABLE ... SHRINK SPACE statement later to complete the operation. This clause is useful if you want to accomplish the shrink operation in two shorter steps rather than one longer step.

For an index or index-organized table, specifying ALTER [INDEX | TABLE] ... SHRINK SPACE COMPACT is equivalent to specifying ALTER [INDEX | TABLE ... COALESCE. The shrink_clause can be cascaded (please refer to the CASCADE clause, which follows) and compacts the segment more densely than does a coalesce operation, which can improve performance. However, if you do not want to release the unused space, then you can use the appropriate COALESCE clause.

CASCADE If you specify CASCADE, then Oracle Database performs the same operations on all dependent objects of table, including secondary indexes on index-organized tables.

Restrictions on the shrink_clause The shrink_clause is subject to the following restrictions:

You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
Segment shrink is not supported for tables with function-based indexes or bitmap join indexes.
This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
You cannot specify this clause for a compressed table.
You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.

9.小结
释放表中因delete操作产生的碎片方法很多，不局限在文中的shrink方法，更常见的有move表或使用CTAS重建表。
每一种方法都有其适应范围和注意事项。具体场景具体分析。

Good luck.

secooler
10.09.10

-- The End --

ITPub博客

【SHRINK】只有启动行移动才可以使用shrink完成空块释放

最新文章