玉面飞龙的BLOG

Oracle10g提供在线回收数据段空间Shrinking Database Segments Online 的功能，就是将High Water Mark下的数据重新整理，减少数据段中的“漏洞”，从而降低HWM；从CBO来讲，Full Table Scan需要扫描的段就更短。

当然还有一个方法，就是online redefination,也可以重新整理数据段。

Online Shrink的实质就是对表进行DML语句，将接近HWM的纪录移动到段头上。在一个40M的表上测试，该表包括一个索引，随机删除一半数据，Online Shrink后表段大约20M，但期间产生了大约160M的Redo.类似coalesce index，较难在实际环境中使用。

SQL>  create table t tablespace sm_data nologging  as select o.* from dba_objects o,dba_users,dba_tablespaces order by o.object_id;
Table created.

SQL> select bytes from user_segments  where  segment_name=’T';
BYTES
———-
46530560

SQL> create index tidx on t (object_id) tablespace sm_data nologging;
Index created.

SQL> select min(object_id),max(object_id) from t;
MIN(OBJECT_ID) MAX(OBJECT_ID)
————– ————–
2          12104

SQL> delete from t where object_id between 1 and 2000;
87885 rows deleted.
SQL>  delete from t where object_id between 7000 and 8000;
43830 rows deleted.
SQL> delete from t where object_id between 3500 and 4500;
44370 rows deleted.
SQL> delete from t where object_id between 9500 and 10500;
10710 rows deleted.
SQL> commit;

SQL> select  * from v$Mystat where STATISTIC#=134;
SID STATISTIC#      VALUE
———- ———- ———-
326        134          0

SQL> alter table t shrink space;
alter table t shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

Elapsed: 00:00:00.03

SQL> alter table t enable row movement;
Table altered.

Elapsed: 00:00:00.02
SQL>  alter table t shrink space;
Table altered.

Elapsed: 00:02:25.27
SQL>  select  * from v$Mystat where STATISTIC#=134;
SID STATISTIC#      VALUE
———- ———- ———-
326        134  165723568

Elapsed: 00:00:00.01

SQL>  select bytes from user_segments  where  segment_name=’T';
BYTES
———-
25690112
Elapsed: 00:00:00.11