---很多时候，当表进行了频繁的操作以后，特别是当表中的大量数据被delete掉以后，表的高水位并不会降低，表空间自然也下不来，有的时候我们可能需要手工的去处理以释放表空间，这样也有助于提高性能。
下面通过一个例子来说明：
[@more@]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--------------------------------------------------
转自 http://blog.itpub.net/24930246/viewspace-1058527/