问题现象
OceanBase 数据库 V3.x 版本中通过 dbms_metadata.get_ddl 函数包无法获取二级分区表 USING INDEX LOCAL 的 unique 约束。
示例。
创建表 SUBS_SERVICE。
obclient [SYS]> CREATE TABLE "SUBS_SERVICE" ( "KEY_ID" NUMBER(13) CONSTRAINT "SUBS_SERVICE_OBNOTNULL_1684227984124400" NOT NULL ENABLE, "SERVICE_ID" VARCHAR2(16), "PACKAGE_ID" VARCHAR2(4), "AVAILTIME" DATE CONSTRAINT "SUBS_SERVICE_OBNOTNULL_1684227984124420" NOT NULL ENABLE, "EXPIRETIME" DATE, "OID" NUMBER(18) DEFAULT 0 CONSTRAINT "SUBS_SERVICE_OBNOTNULL_1684227984124426" NOT NULL ENABLE, "AUDIT_CHANGE_TIME" DATE, CONSTRAINT "PK_SUBS_SERVICE" UNIQUE ("KEY_ID", "OID") USING INDEX LOCAL ) partition by range("KEY_ID") subpartition by hash("KEY_ID") (partition PART535 values less than (5359999999999) ( subpartition P_535_01, subpartition P_535_02, subpartition P_535_03, subpartition P_535_04, subpartition P_535_05, subpartition P_535_06, subpartition P_535_07, subpartition P_535_08, subpartition P_535_09, subpartition P_535_10, subpartition P_535_11, subpartition P_535_12, subpartition P_535_13, subpartition P_535_14, subpartition P_535_15, subpartition P_535_16)); Query OK, 0 rows affected (0.203 sec)通过
dbms_metadata.get_ddl函数包获取表结构信息时，未展示 unique 约束。obclient [SYS]> select dbms_metadata.get_ddl('TABLE','SUBS_SERVICE','SYS') from dual;输出结果如下：
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | DBMS_METADATA.GET_DDL('TABLE','SUBS_SERVICE','SYS') | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CREATE TABLE "SUBS_SERVICE" ( "KEY_ID" NUMBER(13) CONSTRAINT "SUBS_SERVICE_OBNOTNULL_1684227984124400" NOT NULL ENABLE, "SERVICE_ID" VARCHAR2(16), "PACKAGE_ID" VARCHAR2(4), "AVAILTIME" DATE CONSTRAINT "SUBS_SERVICE_OBNOTNULL_1684227984124420" NOT NULL ENABLE, "EXPIRETIME" DATE, "OID" NUMBER(18) DEFAULT 0 CONSTRAINT "SUBS_SERVICE_OBNOTNULL_1684227984124426" NOT NULL ENABLE, "AUDIT_CHANGE_TIME" DATE ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 partition by range("KEY_ID") subpartition by hash("KEY_ID") (partition PART535 values less than (5359999999999) ( subpartition P_535_01, subpartition P_535_02, subpartition P_535_03, subpartition P_535_04, subpartition P_535_05, subpartition P_535_06, subpartition P_535_07, subpartition P_535_08, subpartition P_535_09, subpartition P_535_10, subpartition P_535_11, subpartition P_535_12, subpartition P_535_13, subpartition P_535_14, subpartition P_535_15, subpartition P_535_16)) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.562 sec)
问题原因
内核表结构展示逻辑 BUG，导致无法在表结构展示二级分区表的 USING INDEX LOCAL 的 unique 约束。
问题的风险及影响
查询表信息，显示不完整。
影响租户
影响 OceanBase 数据库中的 Oracle 租户，对于 SYS 租户和 MySQL 租户无影响。
适用版本
OceanBase 数据库 V3.x 版本。
解决方法及规避方式
解决方法：
升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库 V3.2.3 BP10 Hotfix7（oceanbase-3.2.3.3-110070012024012316）及之后版本。
规避方式
如需导出表结构场景，可通过 OBDUMPER 导出方式规避。
