1.先查詢資料庫占用磁碟空間的資料表及index
SELECT segment_name,segment_type,extents,bytes,
ROUND(bytes/(1024*1024),1) MBytes
FROM user_segments where extents >100 ORDER BY bytes desc
ROUND(bytes/(1024*1024),1) MBytes
FROM user_segments where extents >100 ORDER BY bytes desc
2.清除資料表的SQL
alter table Table_Name enable row movement;
ALTER TABLE Table_Name SHRINK SPACE COMPACT;
ALTER TABLE Table_Name SHRINK SPACE COMPACT;
ALTER TABLE Table_Name SHRINK SPACE;
alter table Table_Name disable row movement;
3.清除Index的SQL
ALTER INDEX <INDEX_NAME> SHRINK SPACE;
4.上述動作處理完後可以先export再import試試看。
5.若仍然資料沒縮小，可以刪除table space後再重新建立再import。
Drop tablespace table_space_name INCLUDING CONTENTS AND DATAFILES;
