如果分區表先天設計不良，後天要改又很麻煩，造成清除逾期資料無法用truncate partition來處理，只能用delete處理逾期資料，那會因為HWM高水位的問題造成資料表占用空間不斷成長，可以參考以下命令來針對partition table與partition index做搬移與重建，就可以釋放空間囉
--alter table TABLE_NAME truncate partition PARTITION_NAME;
--查看資料表佔用空間
select SUM(BYTES)/1024/1024 SIZE_MB from
DBA_SEGMENTS where SEGMENT_NAME='TABLE_NAME';
--查看索引佔用空間
select sum(bytes)/1024/1024 SIZE_MB from
DBA_segments where segment_name IN (SELECT INDEX_NAME FROM DBA_INDEXES WHERE
TABLE_NAME='TABLE_NAME');
--產生搬移分區表命令
--alter table TABLE_NAME move partition PARTITION_NAME
SELECT 'alter table ' || table_name || ' move partition '
|| partition_name from DBA_TAB_PARTITIONS WHERE
TABLE_NAME= 'TABLE_NAME';
--因為搬移完分區表，分區索引會失效，所以要重建喔
--產生重建分區索引命令
--alter index INDEX_NAME rebuild
partition PARTITION_NAME TABLESPACE
TABLESPACE_NAME
SELECT 'alter index '|| index_name ||' rebuild
partition ' || partition_name || ' TABLESPACE ' || tablespace_name
from DBA_Ind_Partitions WHERE
index_name in (select index_name from dba_part_indexes where
table_name = 'TABLE_NAME');
--檢查是否有無效的索引分區
SELECT * from DBA_Ind_Partitions WHERE index_name in (select index_name from
dba_part_indexes where table_name = 'TABLE_NAME') and status = 'UNUSABLE';
