---很多时候，当表进行了频繁的操作以后，特别是当表中的大量数据被delete掉以后，表的高水位并不会降低，表空间自然也下不来，有的时候我们可能需要手工的去处理以释放表空间，这样也有助于提高性能。

下面通过一个例子来说明：

SQL> create table tyy as select * from dba_objects;

Table created.

SQL> insert into tyy select * from tyy;

71800 rows created.

SQL> /

143600 rows created.

SQL> commit;

Commit complete.

SQL> insert into tyy select * from tyy;

287200 rows created.

SQL> commit;

Commit complete.

SQL> Select owner,table_name,tablespace_name,round(blocks*8192/1024/1024,2) table_size_m,
2 round((avg_row_len*num_rows+(24*ini_trans+100+8192*pct_free/100)*blocks)/8192/blocks*100,2) pct from dba_tables
3 where owner='SYS' and table_name='TYY'
4 order by 4,owner,5;

OWNER TABLE_NAME TABLESPACE_NAME TABLE_SIZE_M PCT
------------------------------ ------------------------------ ------------------------------ ------------ ----------
SYS TYY SYSTEM 31.77 97.21

SQL> exec dbms_stats.gather_table_stats (ownname => 'SYS',tabname => 'TYY',estimate_percent => 50,block_sample => TRUE,method_opt => 'FOR ALL COLUMNS SIZE 1',degree => null,granularity => 'ALL',cascade => true);

PL/SQL procedure successfully completed.

SQL> Select owner,table_name,tablespace_name,round(blocks*8192/1024/1024,2) table_size_m,
2 round((avg_row_len*num_rows+(24*ini_trans+100+8192*pct_free/100)*blocks)/8192/blocks*100,2) pct from dba_tables
3 where owner='SYS' and table_name='TYY'
4 order by 4,owner,5;

OWNER TABLE_NAME TABLESPACE_NAME TABLE_SIZE_M PCT
------------------------------ ------------------------------ ------------------------------ ------------ ----------
SYS TYY SYSTEM 63.46 95.56

SQL> delete from tyy where object_id>=200;

572816 rows deleted.

SQL> select count(*) from tyy;

COUNT(*)
----------
1584

SQL> Select owner,table_name,tablespace_name,round(blocks*8192/1024/1024,2) table_size_m,
2 round((avg_row_len*num_rows+(24*ini_trans+100+8192*pct_free/100)*blocks)/8192/blocks*100,2) pct from dba_tables
3 where owner='SYS' and table_name='TYY'
4 order by 4,owner,5;

OWNER TABLE_NAME TABLESPACE_NAME TABLE_SIZE_M PCT
------------------------------ ------------------------------ ------------------------------ ------------ ----------
SYS TYY SYSTEM 63.46 95.56

SQL> exec dbms_stats.gather_table_stats (ownname => 'SYS',tabname => 'TYY',estimate_percent => 50,block_sample => TRUE,method_opt => 'FOR ALL COLUMNS SIZE 1',degree => null,granularity => 'ALL',cascade => true);

PL/SQL procedure successfully completed.

SQL> Select owner,table_name,tablespace_name,round(blocks*8192/1024/1024,2) table_size_m,
2 round((avg_row_len*num_rows+(24*ini_trans+100+8192*pct_free/100)*blocks)/8192/blocks*100,2) pct from dba_tables
3 where owner='SYS' and table_name='TYY'
4 order by 4,owner,5;

OWNER TABLE_NAME TABLESPACE_NAME TABLE_SIZE_M PCT
------------------------------ ------------------------------ ------------------------------ ------------ ----------
SYS TYY SYSTEM 63.46 11.69

SQL> alter table tyy move;

Table altered.

SQL> Select owner,table_name,tablespace_name,round(blocks*8192/1024/1024,2) table_size_m,
2 round((avg_row_len*num_rows+(24*ini_trans+100+8192*pct_free/100)*blocks)/8192/blocks*100,2) pct from dba_tables
3 where owner='SYS' and table_name='TYY'
4 order by 4,owner,5;

OWNER TABLE_NAME TABLESPACE_NAME TABLE_SIZE_M PCT
------------------------------ ------------------------------ ------------------------------ ------------ ----------
SYS TYY SYSTEM 63.46 11.69

SQL> exec dbms_stats.gather_table_stats (ownname => 'SYS',tabname => 'TYY',estimate_percent => 50,block_sample => TRUE,method_opt => 'FOR ALL COLUMNS SIZE 1',degree => null,granularity => 'ALL',cascade => true);

PL/SQL procedure successfully completed.

SQL> Select owner,table_name,tablespace_name,round(blocks*8192/1024/1024,2) table_size_m,
2 round((avg_row_len*num_rows+(24*ini_trans+100+8192*pct_free/100)*blocks)/8192/blocks*100,2) pct from dba_tables
3 where owner='SYS' and table_name='TYY'
4 order by 4,owner,5;

OWNER TABLE_NAME TABLESPACE_NAME TABLE_SIZE_M PCT
------------------------------ ------------------------------ ------------------------------ ------------ ----------
SYS TYY SYSTEM .14 91.01

SQL>
-------------------End--------------------------------------------------