oracle 測試 清除分割槽資料，索引釋放空間

DROP TABLE t_part;
create table t_part(object_id int,object_name varchar2(1000)) partition by range(object_id)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000),
partition p4 values less than (4000),
partition pm values less than (maxvalue)
);


--插入測試資料
INSERT INTO t_part SELECT ROWNUM,t.index_name FROM dba_indexes t;
commit;


--建立本地分割槽索引
create index IDX_PART_LOCAL on t_part(object_name) local;


--清除分割槽前，檢視索引佔用的空間 1600K
SELECT SEGMENT_NAME AS INDEX_NAME,
         SUM(BYTES) / (1024) "INDEX_SIZE[KB]"
    FROM DBA_SEGMENTS
   WHERE SEGMENT_NAME = 'IDX_PART_LOCAL'
   GROUP BY SEGMENT_NAME;


ALTER TABLE t_part TRUNCATE PARTITION p4 DROP STORAGE;


--清除分割槽後，檢視索引佔用的空間 1344K
SELECT SEGMENT_NAME AS INDEX_NAME,
         SUM(BYTES) / (1024) "INDEX_SIZE[KB]"
    FROM DBA_SEGMENTS
   WHERE SEGMENT_NAME = 'IDX_PART_LOCAL'
   GROUP BY SEGMENT_NAME;