段收缩

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index index_t on t(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from user_tables where table_name='T';

  NUM_ROWS     BLOCKS
---------- ----------
     72127       1053

SQL> delete t where rownum<30000;

29999 rows deleted.

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);---收集统计信息cascade连同收集index

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from user_tables where table_name='T';

  NUM_ROWS     BLOCKS
---------- ----------
     42128       1053

SQL> alter table t enable row movement;

Table altered.

SQL> alter table t shrink space compact;

Table altered.

SQL> alter table t shrink space;

Table altered.

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from user_tables where table_name='T';

  NUM_ROWS     BLOCKS
---------- ----------
     42128        610

SQL> select status from user_indexes where index_name='INDEX_T';----自动维护索引

STATUS
--------
VALID