收缩数据表空间(move & shrink)+索引表空间(rebuild)

1、数据表空间

     a、收缩此表空间中的table

      alter table ××   enable row movement;

      alter table ××  shrink space; or  alter table ×× move; --move后需重建索引

     b、生成收缩数据文件脚本

SELECT
a.file_id,
a.file_name
file_name,
CEIL( ( NVL( hwm,1 ) * blksize ) / 1024 / 1024 ) smallest,
CEIL( blocks * blksize / 1024 / 1024 ) currsize,
CEIL( blocks * blksize / 1024 / 1024 ) -
CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) savings,
'alter database datafile ''' || file_name || ''' resize ' ||
CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) || 'm;' cmd
FROM
DBA_DATA_FILES a,
(
SELECT file_id, MAX( block_id + blocks - 1 ) hwm
FROM DBA_EXTENTS
GROUP BY file_id
) b,
(
SELECT TO_NUMBER( value ) blksize
FROM V$PARAMETER
WHERE name = 'db_block_size'
) WHERE
a.file_id = b.file_id(+)
ANd CEIL( blocks * blksize / 1024 / 1024 ) - CEIL( ( NVL( hwm, 1 ) * blksize ) / 1024 / 1024 ) > 0
ORDER BY 5 desc；

2、索引表空间

   a、创建辅助表空间test

   b、rebuild 到另外一个表空间

      

   select 'alter index '||w.OWNER||'.'||segment_name||' rebuild  tablespace tbs_ls_amort_idx nologging;' FROM DBA_SEGMENTS w  WHERE w.TABLESPACE_NAME LIKE '%TEST%';

   c、删除原来表空间  

drop tablespace ×× including contents and datafiles；

 d、重新创建原来表空间

  e、再rebuild过去

  select 'alter index '||w.OWNER||'.'||segment_name||' rebuild  tablespace tbs_ls_amort_idx nologging;' FROM DBA_SEGMENTS w WHERE w.TABLESPACE_NAME LIKE '%TEST%';