alter table/index/materialized view object_name shrink space [cascade] [compact];
SYS@TEST16>create table t as select * from dba_objects;
Table created.
SYS@TEST16>create index i on t(object_id);
Index created.
查看表和索引的空间使用情况：
SYS@TEST16>select segment_name,segment_type,bytes,blocks from dba_segments where owner='TEST' and segment_name in
2 (select 'T' from dual UNION ALL select index_name from dba_indexes where table_owner='TEST' and TABLE_NAME='T');
SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
------------------------------ ------------------ ---------- ----------
T TABLE 9437184 1152
I INDEX 2097152 256
删除表数据，发现表和索引的空间并没有被收回：
SYS@TEST16>delete from t;
72568 rows deleted.
SYS@TEST16>commit;
Commit complete.
SYS@TEST16>select segment_name,segment_type,bytes,blocks from dba_segments where owner='TEST' and segment_name in
2 (select 'T' from dual UNION ALL select index_name from dba_indexes where table_owner='TEST' and TABLE_NAME='T');
SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
------------------------------ ------------------ ---------- ----------
T TABLE 9437184 1152
I INDEX 2097152 256
直到做了段收缩之后，空间才被真正收回：
SYS@TEST16>alter table t enable row movement;
Table altered.
SYS@TEST16>alter table t shrink space cascade compact;
Table altered.
SYS@TEST16>alter table t shrink space cascade;
Table altered.
SYS@TEST16>select segment_name,segment_type,bytes,blocks from dba_segments where owner='TEST' and segment_name in
2 (select 'T' from dual UNION ALL select index_name from dba_indexes where table_owner='TEST' and TABLE_NAME='T');
SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
------------------------------ ------------------ ---------- ----------
T TABLE 65536 8
I INDEX 65536 8
推荐阅读：