收缩表空间

以T20111113为例,有索引,索引名为I_T20111113_ID;

alter table T20111113 move;


alter table t20111113 enable row movement;
alter table T20111113 shrink space;

alter index "BIN$sZ7XZPrJWh/gQKjAChI9+w==$0" shrink space;

analyze table t20111113 compute statistics;

空间总的大小和实际使用大小

 

select 'alter database datafile ''' || a.file_name || ''' resize ' ||

       round(a.filesize - (a.filesize - c.hwmsize - 100) * 0.8) || 'M;',
       a.filesize || 'M' as "数据文件的总大小",
       c.hwmsize || 'M' as "数据文件的实用大小"
  from (select file_id, file_name, round(bytes / 1024 / 1024) as filesize
          from dba_data_files) a,
       (select file_id, round(max(block_id) * 8 / 1024) as HWMsize
          from dba_extents
         group by file_id) c

 where a.file_id = c.file_id

其中的move 以及shrink space 解释可查阅其他文章