【实验】【PARTITION】RANGE分区表增加分区

1.增加分区的语法

alter table table_name ...

2.创建⼀个分区表

sec@ora10g> drop table t_partition_range purge;Table dropped.

sec@ora10g> create table t_partition_range (id number,name varchar2(50))2 partition by range(id)(

3 partition t_range_p1 values less than (10) tablespace tbs_part01,4 partition t_range_p2 values less than (20) tablespace tbs_part02,5 partition t_range_p3 values less than (30) tablespace tbs_part036 );

Table created.

sec@ora10g> col TABLE_NAME for a20sec@ora10g> col partition_name for a20sec@ora10g> col HIGH_VALUE for a10

sec@ora10g> col TABLESPACE_NAME for a15

sec@ora10g> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions wheretable_name='T_PARTITION_RANGE' order by partition_position;

TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME-------------------- -------------------- ---------- ---------------T_PARTITION_RANGE T_RANGE_P1 10 TBS_PART01T_PARTITION_RANGE T_RANGE_P2 20 TBS_PART02T_PARTITION_RANGE T_RANGE_P3 30 TBS_PART03

3.添加⼀个分区t_range_p4

sec@ora10g> alter table t_partition_range add partition t_range_p4 values less than(40) tablespace tbs_part04;Table altered.

sec@ora10g> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions wheretable_name='T_PARTITION_RANGE' order by partition_position;

TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME-------------------- -------------------- ---------- ---------------T_PARTITION_RANGE T_RANGE_P1 10 TBS_PART01T_PARTITION_RANGE T_RANGE_P2 20 TBS_PART02T_PARTITION_RANGE T_RANGE_P3 30 TBS_PART03T_PARTITION_RANGE T_RANGE_P4 40 TBS_PART04从这个结果可以看到t_range_p4分区已经创建成功

4.命题：如果在创建RANGE分区表的时候指定了maxvalue，不可以添加分区（需要使⽤split⽅法来处理）5.实验证明之

6.创建带有maxvalue的分区表

sec@ora10g> drop table t_partition_range purge;Table dropped.

sec@ora10g> create table t_partition_range (id number,name varchar2(50))2 partition by range(id)(

3 partition t_range_p1 values less than (10) tablespace tbs_part01,4 partition t_range_p2 values less than (20) tablespace tbs_part02,5 partition t_range_p3 values less than (30) tablespace tbs_part03,

6 partition t_range_pmax values less than (maxvalue) tablespace tbs_part04);Table created.

7.此时添加分区时会报如下的错误

sec@ora10g> alter table t_partition_range add partition t_range_p4 values less than(40) tablespace tbs_part04;alter table t_partition_range add partition t_range_p4 values less than(40) tablespace tbs_part04 *ERROR at line 1:

ORA-14074: partition bound must collate higher than that of the last partition

难道针对这样的分区表就不能修改添加分区了么？对于强⼤的来说那是不可能的，处理⽅法是使⽤split的⽅法来处理之。8.展⽰使⽤split完成上⾯没有完成的分区任务

sec@ora10g> alter table t_partition_range split partition t_range_pmax at (40) into (partition tbs_part05, partition t_range_pmax);Table altered.

sec@ora10g> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions wheretable_name='T_PARTITION_RANGE' order by partition_position;

TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME-------------------- -------------------- ---------- ----------------T_PARTITION_RANGE T_RANGE_P1 10 TBS_PART01T_PARTITION_RANGE T_RANGE_P2 20 TBS_PART02T_PARTITION_RANGE T_RANGE_P3 30 TBS_PART03T_PARTITION_RANGE T_RANGE_P4 40 TBS_PART05

T_PARTITION_RANGE T_RANGE_PMAX MAXVALUE TBS_PART04OK，搞定。