适用范围

OB ORACLE租户

概述

梳理ORACLE迁移到OB后分区维护的差异。

OB与ORACLE分区维护的差异

1、OB不支持RENAME PARTITION，在日常维护中，经常有发现在分区命名错误的，不规范的，需要改分区名，就需要把分区删了重建。
2、OB迁移过去的带子分区的表，没有迁移子分区模板，创建表时没有指定SUBPARTITION TEMPLATE,后续不允许设置、修改子分区模板。
3、OB不支持INTERVAL分区，需要人工ADD PARTITION。
4、OB不支持REBUILD INDEX ,索引失效后需要删了重建。—如果OB不支持REBUILD INDEX，但是建议DROP /TRUNCATE PARTITION时加UPDATE GLOBAL INDEX，应该把这个UPDATE GLOBAL INDEX作为默认选项，少数场景下不需要UPDATE GLOBAL INDEX时，语法上有个NOT UPDATE GLOBAL INDEX

5、OB不支持SPLIT语法。

split partition需求在OB中的解决方案

OB没有SPLIT PARTITION语法，但提供了解决方法。

存在MAX分区，或符合列分区时，需要通过SPLIT PARTITION来增加分区，可以调整参数_enable_add_between_range_partitions =True，允许在range分区之间ADD PARTITION，间接实现SPLIT PARTITION的功能，但MAX分区中的数据不会移动。需要人工迁移数据。

示例

注意必须指定租户
alter system set _enable_add_between_range_partitions ='True'  tenant='TEST';

create table r_range_test1(id number,c varchar2(100)) partition by range(id)
(partition p0 values less than(100),
partition pmax values less than(maxvalue));

Insert into r_range_test1 values (50,'test');
Insert into r_range_test1 values (150,'test');

--在p0,pmax之间add partition
obclient [TEST]>alter table r_range_test1 add partition p100 values less than(200);
Query OK, 0 rows affected (0.099 sec)

--在p0前面add partition
obclient [TEST]>alter table r_range_test1 add partition p50 values less than(50);
Query OK, 0 rows affected (0.099 sec)

--add重复分区名
obclient [TEST]>alter table r_range_test1 add partition p100 values less than(200);
ORA-00600: internal error code, arguments: -5280, Duplicate partition name 'P100’

--add重复的high value
alter table r_range_test1 add partition p100_2 values less than(200);
ORA-14074: partition bound must collate higher than that of the last partition

obclient [TEST]> select PARTITION_POSITION,PARTITION_NAME,HIGH_VALUE from dba_tab_partitions where table_name=upper('r_range_test1');
+--------------------+----------------+------------+
| PARTITION_POSITION | PARTITION_NAME | HIGH_VALUE |
+--------------------+----------------+------------+
|                  1 | P50            | 50         |
|                  2 | P0             | 100        |
|                  3 | P100           | 200        |
|                  4 | PMAX           | MAXVALUE   |
+--------------------+----------------+------------+

 
可以看到id=150的记录应该在p100中，但数据仍然在PMAX中，需要人工迁移数据

obclient [TEST]> select * from r_range_test1 partition(PMAX);
+------+------+
| ID   | C    |
+------+------+
|  150 | test |
+------+------+
1 row in set (0.005 sec)


obclient [TEST]> select PARTITION_POSITION,PARTITION_NAME,HIGH_VALUE from dba_tab_partitions where table_name=upper('r_range_test1');
+--------------------+----------------+------------+
| PARTITION_POSITION | PARTITION_NAME | HIGH_VALUE |
+--------------------+----------------+------------+
|                  1 | P50            | 50         |
|                  2 | P0             | 100        |
|                  3 | P100           | 200        |
|                  4 | PMAX           | MAXVALUE   |
+--------------------+----------------+------------+
4 rows in set (0.128 sec)

obclient [TEST]> select * from r_range_test1 where id between 100 and 200;
+------+------+
| ID   | C    |
+------+------+
|  150 | test |
+------+------+
1 row in set (0.003 sec)	

obclient [TEST]> select * from r_range_test1 where id between 100 and 180;
Empty set (0.003 sec)

子分区模板

创建表时没有指定SUBPARTITION TEMPLATE,后续不允许设置、修改子分区模板

DROP TABLE TEST.TEST_SUBPART2

CREATE TABLE TEST.TEST_SUBPART2 (
  "CYCLE" VARCHAR2(48),
  "CODE" NUMBER(5),
  "c" VARCHAR2(64)
) partition by range("CYCLE") subpartition by list("CODE")
SUBPARTITION TEMPLATE (
SUBPARTITION "SUB111" VALUES ( 111 ),
SUBPARTITION "SUB222" VALUES ( 222 ),
SUBPARTITION "SUBOTHER" VALUES ( default )
)
(partition P201712 values less than ('201801'))

---分区表创建后，不能设置，修改子分区模板
ALTER TABLE TEST.TEST_SUBPART  SUBPARTITION TEMPLATE (
SUBPARTITION "SUB110" VALUES ( 110 ),
SUBPARTITION "SUB111" VALUES ( 111 ),
SUBPARTITION "SUB112" VALUES ( 112 ),
SUBPARTITION "SUB113" VALUES ( 113 ),
SUBPARTITION "SUB114" VALUES ( 114 ),
SUBPARTITION "SUB115" VALUES ( 115 ),
SUBPARTITION "SUB116" VALUES ( 116 ),
SUBPARTITION "SUB117" VALUES ( 117 ),
SUBPARTITION "SUB118" VALUES ( 118 ),
SUBPARTITION "SUB119" VALUES ( 119 ),
SUBPARTITION "SUB135" VALUES ( 135 ),
SUBPARTITION "SUB136" VALUES ( 136 ),
SUBPARTITION "SUB199" VALUES ( 199 ),
SUBPARTITION "SUBOTHER" VALUES ( default )
)

ErrorCode = 900, SQLState = 42000, Details = ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'SUBPARTITION TEMPLATE ( SUBPARTITION "SUB310" VALUES ( 310 ), SUBPARTITION "SUB3' at line 1