本文共 3536 字，大约阅读时间需要 11 分钟。

   
    
    
    
      alter table[tablename] drop partition[ptname];
    
    
    
      alter table[tablename] drop subpartition[ptname];
    
    
    
      --default
    
    
    
      SQL> alter table t_partition_list drop partition t_list_default;
    
    
    
    
      SQL> select partition_name,high_value,tablespace_name from user_tab_partitions
    
    
      2 where table_name='T_PARTITION_LIST';
    
    
      PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
    
    
      ------------------------------ ---------------------------------------------------------------------
    
    
      T_LIST_DEFAULT default TBS03
    
    
      T_LIST_P1 1, 3, 5, 7, 9 TBS01
    
    
      T_LIST_P2 2, 4, 6, 8, 10 TBS02
    
    
      T_LIST_P3 21, 23, 25, 27, 29 TBS03
    
    
      T_LIST_P4 20, 22, 24 TBS03
    
    
      --default
    
    
    
      SQL> alter table t_partition_list add partition t_list_default values(default) tablespace tbs03;
    
    
    
      3.4 Merge partitions
    
    
    
      alter table tbname merge partitions/subpartitions pt1,pt2 into partition/subpartition pt3;
    
    
    
      SQL> select partition_name,high_value,tablespace_name from user_tab_partitions
    
    
      2 where table_name='T_PARTITION_LIST';
    
    
      PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
    
    
      ------------------------------ ---------------------------------------------------------------------
    
    
      T_LIST_DEFAULT default TBS03
    
    
      T_LIST_P1 1, 3, 5, 7, 9 TBS01
    
    
      T_LIST_P2 2, 4, 6, 8, 10 TBS02
    
    
      T_LIST_P3 21, 23, 25, 27, 29 TBS03
    
    
      T_LIST_P4 20, 22, 24 TBS03
    
    
      SQL> alter table t_partition_list merge partitions t_list_p4,t_list_default into partition t_list_default;
    
    
    
      SQL> select partition_name,high_value,tablespace_name from user_tab_partitions
    
    
      2 where table_name='T_PARTITION_LIST';
    
    
      PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
    
    
      ------------------------------ ---------------------------------------------------------------------
    
    
      T_LIST_DEFAULT default JJJG
    
    
      T_LIST_P1 1, 3, 5, 7, 9 TBS01
    
    
      T_LIST_P2 2, 4, 6, 8, 10 TBS02
    
    
      T_LIST_P3 21, 23, 25, 27, 29 TBS03
    
    
      3.5 Exchange partition
    
    
    
      alter table tbname1 exchange partition/subpartition ptname with table tbname2;
    
    
    
      SQL> insert into t_partition_list values(1,'a');
    
    
    
      SQL> insert into t_partition_list values(2,'b');
    
    
    
      SQL> insert into t_partition_list values(33,'c');
    
    
    
      SQL> commit;
    
    
    
      SQL> select * from t_partition_list;
    
    
    
      ID NAME
    
    
      ---------- --------------------
    
    
      1 a
    
    
    
      2 b
    
    
    
      33 c
    
    
    
      SQL> select * from t_partition_list partition(t_list_p2);
    
    
    
      ID NAME
    
    
      ---------- --------------------
    
    
      2 b
    
    
    
      3.6 Modify partition
    
    
    
      3.6.1 Add values
    
    
    
      alter table tbname modify partition/subpartition ptname add values (v1,v2....vn);
    
    
    
      SQL> alter table t_partition_list modify partition t_list_p1 add values(11,12);
    
    
    
      SQL> alter table t_partition_list modify partition t_list_p1 add values(2);
    
    
    
      SQL> alter table t_partition_list modify partition t_list_p1 add values(33);
    
    
    
      3.6.2 Drop values
    
    
    
      alter table tbname modify partition/subpartition ptname drop values(v1,v2