1.查看表空间使用率，对比压缩前后以及索引重建前后表空间的一个变化
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1
2.查看数据文件大小(rac查看asm磁盘大小)，方便resize完数据文件对比大小
SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name;
3.创建分区测试表
create table sales_part_test
(
prod_id NUMBER not null,
cust_id NUMBER not null,
time_id DATE not null,
channel_id NUMBER not null,
promo_id NUMBER not null,
quantity_sold NUMBER(10,2) not null,
amount_sold NUMBER(10,2) not null
)
partition by range(time_id) subpartition by range (time_id) --指定主表分区和子分区分区方式都是:范围分区，并按照列time_id 进行范围划分
(
partition sales_part_1998 values less than (TO_DATE('1999-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace HZH0528
(
subpartition sales_part_1998_01 values less than ( TO_DATE('1998-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,--指定主分区sales_part_1998子分区 sales_part_1998_01 注意每个主分区的子分区名字不能一样
subpartition sales_part_1998_02 values less than ( TO_DATE('1998-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1998_03 values less than ( TO_DATE('1998-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1998_04 values less than ( TO_DATE('1998-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1998_05 values less than ( TO_DATE('1998-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1998_06 values less than ( TO_DATE('1998-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1998_07 values less than ( TO_DATE('1998-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1998_08 values less than ( TO_DATE('1998-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1998_09 values less than ( TO_DATE('1998-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1998_10 values less than ( TO_DATE('1998-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1998_11 values less than ( TO_DATE('1998-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1998_12 values less than ( TO_DATE('1999-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528
)
,
partition sales_part_1999 values less than (TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace HZH0528
(
subpartition sales_part_1999_01 values less than ( TO_DATE('1999-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1999_02 values less than ( TO_DATE('1999-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1999_03 values less than ( TO_DATE('1999-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1999_04 values less than ( TO_DATE('1999-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1999_05 values less than ( TO_DATE('1999-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1999_06 values less than ( TO_DATE('1999-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1999_07 values less than ( TO_DATE('1999-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1999_08 values less than ( TO_DATE('1999-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1999_09 values less than ( TO_DATE('1999-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1999_10 values less than ( TO_DATE('1999-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1999_11 values less than ( TO_DATE('1999-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_1999_12 values less than ( TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528
),
partition sales_part_2000 values less than (TO_DATE('2001-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace HZH0528
(
subpartition sales_part_2000_01 values less than ( TO_DATE('2000-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2000_02 values less than ( TO_DATE('2000-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2000_03 values less than ( TO_DATE('2000-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2000_04 values less than ( TO_DATE('2000-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2000_05 values less than ( TO_DATE('2000-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2000_06 values less than ( TO_DATE('2000-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2000_07 values less than ( TO_DATE('2000-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2000_08 values less than ( TO_DATE('2000-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2000_09 values less than ( TO_DATE('2000-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2000_10 values less than ( TO_DATE('2000-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2000_11 values less than ( TO_DATE('2000-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2000_12 values less than ( TO_DATE('2001-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528
),
partition sales_part_2001 values less than (TO_DATE('2002-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace HZH0528 (
subpartition sales_part_2001_01 values less than ( TO_DATE('2001-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2001_02 values less than ( TO_DATE('2001-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2001_03 values less than ( TO_DATE('2001-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2001_04 values less than ( TO_DATE('2001-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2001_05 values less than ( TO_DATE('2001-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2001_06 values less than ( TO_DATE('2001-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2001_07 values less than ( TO_DATE('2001-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2001_08 values less than ( TO_DATE('2001-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2001_09 values less than ( TO_DATE('2001-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2001_10 values less than ( TO_DATE('2001-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2001_11 values less than ( TO_DATE('2001-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528,
subpartition sales_part_2001_12 values less than ( TO_DATE('2002-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) tablespace HZH0528
)
)
4.创建分区局部索引
create index idx_sales_sales_part_test on sales_part_test (time_id)local;
5.插入数据
insert into sales_part_test select * from sh.sales;
6.分析表
analyze table sales_part_test compute statistics;
7.查看分区表是否压缩,以及生成压缩语句
select table_owner,table_name,partition_name,subpartition_name,tablespace_name,compression,compress_for,'alter table '||TABLE_OWNER||'.'||TABLE_NAME||' move subpartition '||SUBPARTITION_NAME||' tablespace '||TABLESPACE_NAME|| ' compress for oltp parallel 4;' from dba_tab_subpartitions where table_name = 'SALES_PART_TEST' ORDER BY SUBPARTITION_NAME;
8.执行子分区压缩，这种压缩尽量放在后台执行
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1998_01 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1998_02 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1998_03 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1998_04 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1998_05 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1998_06 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1998_07 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1998_08 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1998_09 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1998_10 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1998_11 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1998_12 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1999_01 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1999_02 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1999_03 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1999_04 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1999_05 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1999_06 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1999_07 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1999_08 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1999_09 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1999_10 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1999_11 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_1999_12 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2000_01 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2000_02 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2000_03 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2000_04 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2000_05 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2000_06 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2000_07 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2000_08 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2000_09 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2000_10 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2000_11 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2000_12 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2001_01 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2001_02 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2001_03 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2001_04 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2001_05 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2001_06 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2001_07 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2001_08 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2001_09 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2001_10 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2001_11 tablespace HZH0528 compress for oltp parallel 4;
alter table HZH.SALES_PART_TEST move subpartition SALES_PART_2001_12 tablespace HZH0528 compress for oltp parallel 4;
9.查看压缩状态
select table_owner,table_name,partition_name,subpartition_name,tablespace_name,compression,compress_for,'alter table '||TABLE_OWNER||'.'||TABLE_NAME||' move subpartition '||SUBPARTITION_NAME||' tablespace '||TABLESPACE_NAME|| ' compress for oltp parallel 4;' from dba_tab_subpartitions where table_name = 'SALES_PART_TEST' ORDER BY SUBPARTITION_NAME;
10.查看索引状态，此时如果不知道索引名字，可以从dba_indexes视图中去查询表下有无索引
select b.index_name, b.partition_name, b.subpartition_name,b.status from dba_ind_subpartitions b where b.index_name in ('IDX_SALES_SALES_PART_TEST') and status<>'USABLE' order by 1,2;
11.生成索引重建语句
select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE,SUBPARTITION_POSITION,STATUS,TABLESPACE_NAME,'alter index '||INDEX_OWNER||'.'||INDEX_NAME||' rebuild subpartition '||subpartition_name||' parallel 4 online;' from dba_ind_subpartitions where index_name='IDX_SALES_SALES_PART_TEST' and index_owner='HZH' and status<>'USABLE'
alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1998_01 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1998_02 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1998_03 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1998_04 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1998_05 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1998_06 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1998_07 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1998_08 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1998_09 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1998_10 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1998_11 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1998_12 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1999_01 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1999_02 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1999_03 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1999_04 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1999_05 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1999_06 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1999_07 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1999_08 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1999_09 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1999_10 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1999_11 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_1999_12 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2000_01 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2000_02 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2000_03 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2000_04 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2000_05 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2000_06 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2000_07 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2000_08 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2000_09 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2000_10 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2000_11 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2000_12 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2001_01 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2001_02 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2001_03 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2001_04 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2001_05 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2001_06 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2001_07 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2001_08 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2001_09 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2001_10 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2001_11 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST rebuild subpartition SALES_PART_2001_12 parallel 4 online; alter index HZH.IDX_SALES_SALES_PART_TEST noparallel;
12.重新查找重建后的索引状态，这两个语句都可以
select b.index_name, b.partition_name, b.subpartition_name,b.status from dba_ind_subpartitions b where b.index_name in ('IDX_SALES_SALES_PART_TEST') and status<>'USABLE' order by 1,2;
select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,SUBPARTITION_NAME,HIGH_VALUE,SUBPARTITION_POSITION,STATUS,TABLESPACE_NAME,'alter index '||INDEX_OWNER||'.'||INDEX_NAME||' rebuild subpartition '||subpartition_name||' parallel 4 online;' from dba_ind_subpartitions where index_name='IDX_SALES_SALES_PART_TEST' and index_owner='HZH' and status<>'USABLE'
13.压缩完子分区就可以进行数据文件resize，如何resize见另一篇文章，是分区压缩完，数据文件resize的，方式相同
https://www.cnblogs.com/houzhiheng/p/15029461.html