本地分区索引、全局分区索引操作-完成的

--4 分别删除全局分区索引、本地分区索引的某个分区，查看 该索引的状态-----------------------

-------------------------case 1  全局分区索引-------------------------
--4.1 创建分区表
drop table sal_range cascade constraints ;
create table sal_range(n1 number) 
partition by range(n1)
(
partition range_10 values less than(11),
partition range_20 values less than(21),
partition range_30 values less than(31),
partition range_max values less than(maxvalue)
);

--4.2 插入测试数据
declare 
begin 
  for i in 1..40 loop 
    insert into sal_range values(i);
    commit;
    end loop;
    end;    

--4.3 确认表中的数据
select * from sal_range partition (range_10);
select * from sal_range partition (range_20);
select * from sal_range partition (range_30);
select * from sal_range partition (range_max);

--4.4 创建全局分区索引
create index global_ind on sal_range(n1) global
partition by range(n1)
(
partition sal_range_10 values less than(11),
partition sal_range_20 values less than(21),
partition sal_range_30 values less than(31),
partition sal_range_max values less than(maxvalue)
);

--4.5 查询对应分区索引的状态
select  t2.locality,t1.index_name,t1.partition_name,t1.status,t1.tablespace_name from user_ind_partitions t1, user_part_indexes t2  where t1.index_name in ('GLOBAL_IND') and t1.index_name=t2.index_name 
order by index_name,partition_name;

--4.3 删除全局分区索引的一个分区
alter index GLOBAL_IND drop partition sal_range_20;

--4.6 再次确认对应分区索引的状态
select  t2.locality,t1.index_name,t1.partition_name,t1.status,t1.tablespace_name from user_ind_partitions t1, user_part_indexes t2  where t1.index_name in ('GLOBAL_IND') and t1.index_name=t2.index_name 
order by index_name,partition_name;

--删除 分区索引sal_range_20 ，sal_range_30 变为 unusable

-------------------------case 2  本地分区索引-------------------------
--5.1 创建分区表
drop table sal_range cascade constraints ;
create table sal_range01(n1 number) 
partition by range(n1)
(
partition range_10 values less than(11),
partition range_20 values less than(21),
partition range_30 values less than(31),
partition range_max values less than(maxvalue)
);

--4.2 插入测试数据
declare 
begin 
  for i in 1..40 loop 
    insert into sal_range01 values(i);
    commit;
    end loop;
    end;    

--4.3 确认表中的数据
select * from sal_range01 partition (range_10);
select * from sal_range01 partition (range_20);
select * from sal_range01 partition (range_30);
select * from sal_range01 partition (range_max);

--4.4 创建全局分区索引
drop index local_ind
create index local_ind on sal_range01(n1) local
--partition by range(n1)
(partition sal_range_10 ,
 partition sal_range_20 ,
 partition sal_range_30 ,
 partition sal_range_max );

--4.5 查询对应分区索引的状态
select  t2.locality,t1.index_name,t1.partition_name,t1.status,t1.tablespace_name from user_ind_partitions t1, user_part_indexes t2  where t1.index_name in ('LOCAL_IND') and t1.index_name=t2.index_name 
order by index_name,partition_name;

--分区表删除一个分区
alter table sal_range01 drop partition range_20;

--4.3 删除全局分区索引的一个分区
alter index LOCAL_IND drop partition sal_range_10;

--4.6 再次确认对应分区索引的状态
select  t2.locality,t1.index_name,t1.partition_name,t1.status,t1.tablespace_name from user_ind_partitions t1, user_part_indexes t2  where t1.index_name in ('LOCAL_IND') and t1.index_name=t2.index_name 
order by index_name,partition_name;

--删除 分区索引sal_range_20 ，对本地分区索引没有影响