新闻中心> 文章详情
分区表中的maxvalue参数设置
结论：partition p3 values less than (maxvalue) 分区表中maxvalue如果用具体参数来代替，则整个表中可插入的最大值不能超过指定的参数。南京Oracle认证培训
如指定参数是15，则插入的值只能是小于15.
partition p1 values less than (5) 这里的参数5，意味着插入此分区的数值要小于5，不能大于等于。
实验1：使用maxvalue，南京Oracle认证培训
1 2 3 4 5 6 | SQL> create table test1(ab number) partition by range(ab) (partition p1 values less than (5) tablespace users,partition p2 values less than (10) tablespace users2,partition p3 values less than (maxvalue) tablespace users3);Table createdSQL> insert into test1 select rownum from dual connect by rownum<13;12 rows inserted |
实验二：使用指定参数，插入数据验证整个表中可插入的最大值不能超过指定的参数的结论。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | SQL> create table test5(ab number) partition by range(ab) (partition p1 values less than (4) tablespace users,partition p2 values less than (8) tablespace users2,partition p3 values less than (15) tablespace users3);Table createdSQL> select * from test5; AB----------SQL> select * from test5 partition(p1); AB----------SQL> insert into test5 select rownum from dual connect by rownum<17;insert into test5 select rownum from dual connect by rownum<17ORA-14400: inserted partition key does not map to any partitionSQL> insert into test5 select rownum from dual connect by rownum<16;insert into test5 select rownum from dual connect by rownum<16ORA-14400: inserted partition key does not map to any partitionSQL> insert into test5 select rownum from dual connect by rownum<15;14 rows insertedSQL> commit; Commit completeSQL> select * from test5 partition(p1); AB---------- 1 2 3 SQL> select * from test5 partition(p2); AB---------- 4 5 6 7 SQL> select * from test5 partition(p3); AB---------- 8 9 10 11 12 13 14 SQL> select * from test5 ; AB---------- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 14 rows selected |
