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;