截断分区(truncate)/重命名分区(rename)/移动分区(move)

截断分区(truncate):截断分区和drop分区类似，但是区别在于他只是截断数据，并且降低HWM，同时不同还包括其可以对HASH分区使用
限制：1、如果此分区中包含了数据，截断后全局索引和普通索引均失效，除非带上update indexes
      2、本地分区索引不受影响
语法
alter table t_pe_h
  truncate partition p2 update indexes;
alter table t_pe_r
 truncate partition P1 update indexes;
alter table t_pe_l
 truncate partition P1 update indexes;
重命名分区(rename):通过修改数据字典进行重命名，因为不涉及到实际行，所以任何索引不受到其影响，同时可以修改分区索引的名字
限制：无
语法
alter table t_pe_h rename partition p2 to p2_test;
alter index t_pe_h_l rename partition p1 to p1_test;
移动分区（move）:使用移动分区可以完成下面的任务，其会修改ROWID信息。
1、和普通表一样，可以减少碎片，同时降低高水位
2、可以移动分区到另外一个表空间
3、可以修改建立的时候的属性，虽然也可以通过modify分区修改，但是比如表空间这样属性MODIFY 是不能修改的。
4、修改为压缩属性
限制：1、如果MOVE的分区包含数据，那么全局索引和普通索引均失效，除非带上update indexes
      2、MOVE分区的本地索引分区也会失效，除非带上UPDATE INDEXES
      3、如果想修改hash分区的压缩属性和存储属性是不行的。报错ORA-14260: 为该分区指定的物理属性不正确
  alter table t_pe_h
  move partition p2  compress STORAGE (MAXEXTENTS 10)  update indexes
 
ORA-14260: 为该分区指定的物理属性不正确
语法：
alter table t_pe_h
  move partition p2   {update indexes};
alter table t_pe_l
  move partition p1  compress STORAGE (MAXEXTENTS 10) {update indexes};
alter table t_pe_r
  move partition p1  compress STORAGE (MAXEXTENTS 10)  {update indexes};

脚本：
drop table t_pe_l;
drop table t_pe_r;
drop table t_pe_h;

CREATE TABLE t_pe_r (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY RANGE(j)
        (PARTITION p1 VALUES LESS THAN (10),
         PARTITION p2 VALUES LESS THAN (20),
         PARTITION P3 VALUES LESS THAN (30),
         PARTITION P4 VALUES LESS THAN (40),
         PARTITION p5 VALUES LESS THAN (maxvalue));

create index t_pe_r_n on t_pe_r(i);
create index t_pe_r_l on t_pe_r(j) local;
create index t_pe_r_g on t_pe_r(f)
GLOBAL PARTITION BY hash (f)
(partition pg1 ,
 partition pg2);

CREATE TABLE t_pe_l (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY list(j)
        (PARTITION p1 VALUES (10),
         PARTITION p2 VALUES (20),
         PARTITION P3 VALUES (30),
         PARTITION P4 VALUES (40),
         PARTITION p5 VALUES (default));

create index t_pe_l_n on t_pe_l(i);
create index t_pe_l_l on t_pe_l(j) local;
create index t_pe_l_g on t_pe_l(f)
GLOBAL PARTITION BY hash (f)
(partition pg1 ,
 partition pg2);

CREATE TABLE t_pe_h (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY hash(j)
        (PARTITION p1,
         PARTITION p2,
         PARTITION P3,
         PARTITION P4);
create index t_pe_h_n on t_pe_h(i);
create index t_pe_h_l on t_pe_h(j) local;
create index t_pe_h_g on t_pe_h(f)
GLOBAL PARTITION BY hash (f)
(partition pg1 ,
 partition pg2);

insert into t_pe_l
values(2,10,5,'A');
insert into t_pe_l
values(1,20,10,'B');
insert into t_pe_l
values(3,30,40,'C');
insert into t_pe_l
values(2,40,30,'A');
insert into t_pe_l
values(1,40,20,'B');
insert into t_pe_l
values(3,85,50,'C');

insert into t_pe_r
values(2,5,'a','A');
insert into t_pe_r
values(1,15,'b','B');
insert into t_pe_r
values(2,10,'a','A');
insert into t_pe_r
values(1,20,'b','B');
insert into t_pe_r
values(2,5,'a','A');
insert into t_pe_r
values(1,25,'b','B');
insert into t_pe_r
values(3,85,'c','C');

insert into t_pe_h
values(2,5,'a','A');
insert into t_pe_h
values(1,15,'b','B');
insert into t_pe_h
values(2,10,'a','A');
insert into t_pe_h
values(1,20,'b','B');
insert into t_pe_h
values(2,5,'a','A');
insert into t_pe_h
values(1,25,'b','B');
insert into t_pe_h
values(3,85,'c','C');

select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_l';
select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_g';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_r_n';

select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_l';
select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_g';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_l_n';

select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_h_l';
select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_h_g';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_h_n';

alter table t_pe_h
  move partition p2   update indexes;
alter table t_pe_l
  move partition p1  compress STORAGE (MAXEXTENTS 10) update indexes;
alter table t_pe_r
  move partition p1  compress STORAGE (MAXEXTENTS 10)  update indexes;
 
alter table t_pe_h rename partition p2 to p2_test;
alter index t_pe_h_l rename partition p1 to p1_test;

alter table t_pe_h
  truncate partition p2 update indexes;
alter table t_pe_r
 truncate partition P1 update indexes;
alter table t_pe_l
 truncate partition P1 update indexes;

 

 

select INDEX_NAME,LOCALITY from dba_part_indexes where TABLE_NAME='T_PE_R';

alter table t_pe_h
      move partition p2   ;

 

alter index t_pe_h_g rebuild partition pG1;