Oracle 分区表如何添加分区

Oracle  分区表如何添加分区

1.创建个分区表
 SQL> create table p_range_test
   2  (id number,name varchar2(100))
   3  partition by range(id)(
   4  partition t_p1 values less than (10),
   5  partition t_p2 values less than (20),
   6  partition t_p3 values less than (30)
   7  );
 Table created.
 2.查第一步创建好分区表的信息
 SQL> col TABLE_NAME for a20
 SQL> col partition_name for a20
 SQL> col HIGH_VALUE for a10
 SQL> col TABLESPACE_NAME for a15
 SQL> select table_name,partition_name,high_value,tablespace_name 
 from user_tab_partitions where table_name='P_RANGE_TEST' 
 order by partition_position;
 TABLE_NAME           PARTITION_NAME       HIGH_VALUE TABLESPACE_NAME
 -------------------- -------------------- ---------- ---------------
 P_RANGE_TEST         T_P1                 10         TP1
 P_RANGE_TEST         T_P2                 20         TP1
 P_RANGE_TEST         T_P3                 30         TP1
 从上面看出已有3个分区
 3.添加一个分区t_p4
 SQL> alter table p_range_test add partition t_p4 values less than(40);
 Table altered.
顺序添加完成，当然这个不是LZ想要的吧？
 4.再第三步添加好分区的信息
 SQL> select table_name,partition_name,high_value,tablespace_name 
 from user_tab_partitions where table_name='P_RANGE_TEST' 
 order by partition_position;
 TABLE_NAME           PARTITION_NAME       HIGH_VALUE TABLESPACE_NAME
 -------------------- -------------------- ---------- ---------------
 P_RANGE_TEST         T_P1                 10         TP1
 P_RANGE_TEST         T_P2                 20         TP1
 P_RANGE_TEST         T_P3                 30         TP1
 P_RANGE_TEST         T_P4                 40         TP1
 从这个实验结果可以看到t_p4分区已经创建成功
 5.如果在创建RANGE分区表的时候指定了maxvalue,不可以添加分区
 （需要使用split方法来处理）
 6.创建带有maxvalue的分区表
 SQL> drop table p_range_test purge;--purge不回收
 create table p_range_test (id number,name varchar2(100))
 partition by range(id)(
 partition t_p1   values less than (10),
 partition t_p2   values less than (20),
 partition t_p3   values less than (30),
 partition t_pmax values less than (maxvalue));
 Table dropped.
 SQL>   2    3    4    5    6  
 Table created.
 
7.此时添加分区时会报如下的错误
 SQL>  alter table p_range_test add partition t_p4 values less than(40);
 alter table p_range_test add partition t_p4 values less than(40)
                                         *
 ERROR at line 1:
 ORA-14074: partition bound must collate higher than that of the last partition
 添加分区失败？那有没有办法搞定呢,对于牛B的oracle来说肯定有办法了,
 处理方法就是使用split来搞！
 8.再查一下当前的分表的信息
 SQL> select table_name,partition_name,high_value,tablespace_name 
 from user_tab_partitions where table_name='P_RANGE_TEST' 
 order by partition_position;
 TABLE_NAME           PARTITION_NAME       HIGH_VALUE TABLESPACE_NAME
 -------------------- -------------------- ---------- ---------------
 P_RANGE_TEST         T_P1                 10         TP1
 P_RANGE_TEST         T_P2                 20         TP1
 P_RANGE_TEST         T_P3                 30         TP1
 P_RANGE_TEST         T_PMAX               MAXVALUE   TP1
 
9.注意，这步就是关键的一步！！！使用split完成上面没有完成的分区任务
 SQL> alter table p_range_test split partition t_pmax at (40) into 
 (partition T_P4, partition t_pmax);
 Table altered.
 10.再查一下当前的分表的信息
 SQL> select table_name,partition_name,high_value,tablespace_name 
 from user_tab_partitions where table_name='P_RANGE_TEST' 
 order by partition_position;
 TABLE_NAME           PARTITION_NAME       HIGH_VALUE TABLESPACE_NAME
 -------------------- -------------------- ---------- ---------------
 P_RANGE_TEST         T_P1                 10         TP1
 P_RANGE_TEST         T_P2                 20         TP1
 P_RANGE_TEST         T_P3                 30         TP1
 P_RANGE_TEST         T_P4                 40         TP1
 P_RANGE_TEST         T_PMAX               MAXVALUE   TP1
 
 
 http://www.itpub.net/thread-1748738-1-1.html