ORACLE 租户分区表相关系统视图不准导致 OMS 迁移产生数据一致性问题

-- 模版化分区表新增二级分区后，DBA_OBJECTS、DBA_TAB_SUBPARTITIONS 视图查询结果不包含新增二级分区的记录

CREATE TABLE T1(
  col1 NUMBER,
  col2 NUMBER
)
PARTITION BY RANGE(col1)
SUBPARTITION BY RANGE(col2)
SUBPARTITION TEMPLATE
(SUBPARTITION mp0 VALUES LESS THAN(2020),
SUBPARTITION mp1 VALUES LESS THAN(2021),
SUBPARTITION mp2 VALUES LESS THAN(2022)
)
(PARTITION p0 VALUES LESS THAN(100),
PARTITION p1 VALUES LESS THAN(200)

-- 新增分区前相关视图查询结果

SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME FROM SYS.DBA_TAB_SUBPARTITIONS WHERE table_name='T1';
+------------+----------------+-------------------+
| TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME |
+------------+----------------+-------------------+
| T1         | P0             | P0sMP0            |
| T1         | P0             | P0sMP1            |
| T1         | P0             | P0sMP2            |
| T1         | P1             | P1sMP0            |
| T1         | P1             | P1sMP1            |
| T1         | P1             | P1sMP2            |
+------------+----------------+-------------------+
 
SELECT OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE FROM SYS.DBA_OBJECTS WHERE OBJECT_NAME='T1';
+-------------+----------------+--------------------+
| OBJECT_NAME | SUBOBJECT_NAME | OBJECT_TYPE        |
+-------------+----------------+--------------------+
| T1          | NULL           | TABLE              |
| T1          | P0             | TABLE PARTITION    |
| T1          | P1             | TABLE PARTITION    |
| T1          | P0sMP0         | TABLE SUBPARTITION |
| T1          | P0sMP1         | TABLE SUBPARTITION |
| T1          | P0sMP2         | TABLE SUBPARTITION |
| T1          | P1sMP0         | TABLE SUBPARTITION |
| T1          | P1sMP1         | TABLE SUBPARTITION |
| T1          | P1sMP2         | TABLE SUBPARTITION |
+-------------+----------------+--------------------+

-- 新增分区

ALTER TABLE T1 ADD PARTITION p2 VALUES LESS THAN(300); 

-- 新增分区后相关视图查询结果（无新增二级分区的记录）

SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME FROM SYS.DBA_TAB_SUBPARTITIONS WHERE table_name='T1';
+------------+----------------+-------------------+
| TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME |
+------------+----------------+-------------------+
| T1         | P0             | P0sMP0            |
| T1         | P0             | P0sMP1            |
| T1         | P0             | P0sMP2            |
| T1         | P1             | P1sMP0            |
| T1         | P1             | P1sMP1            |
| T1         | P1             | P1sMP2            |
+------------+----------------+-------------------+
 
SELECT OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE FROM SYS.DBA_OBJECTS WHERE OBJECT_NAME='T1';
+-------------+----------------+--------------------+
| OBJECT_NAME | SUBOBJECT_NAME | OBJECT_TYPE        |
+-------------+----------------+--------------------+
| T1          | NULL           | TABLE              |
| T1          | P0             | TABLE PARTITION    |
| T1          | P1             | TABLE PARTITION    |
| T1          | P2             | TABLE PARTITION    |
| T1          | P0sMP0         | TABLE SUBPARTITION |
| T1          | P0sMP1         | TABLE SUBPARTITION |
| T1          | P0sMP2         | TABLE SUBPARTITION |
| T1          | P1sMP0         | TABLE SUBPARTITION |
| T1          | P1sMP1         | TABLE SUBPARTITION |
| T1          | P1sMP2         | TABLE SUBPARTITION |
+-------------+----------------+--------------------+