Oceanbase V3 的 dba_objects 视图针对表分区的 CREATED 字段显示不准确

概念描述

在 Oceanbase V3.2.4 上，对分区表的分区执行ddl操作，会把操作时间更新到 dba_objects 视图的 CREATED 字段上，这是不符合逻辑的，失去了 CREATED 字段的含义。

测试验证

• 创建测试数据

[root@ocp ~]# obsys
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 64694
Server version: OceanBase 3.2.4.5 (r105040022023111323-57088ca24eab4348125068a9ccf182d068a6a883) (Built Nov 13 2023 23:56:19)

Copyright (c) 2000, 2018, OB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [SYS]> drop table dbmt.tablea purge;
Query OK, 0 rows affected (0.160 sec)

obclient [SYS]> create table dbmt.tablea(id number,c varchar2(100)) partition by range(id) (partition p1 values less than(100), partition p2 values less than(200));
Query OK, 0 rows affected (0.090 sec)

obclient [SYS]> insert into dbmt.tablea select rownum,'test' from dual connect by rownum<200;
Query OK, 199 rows affected (0.023 sec)
Records: 199  Duplicates: 0  Warnings: 0

obclient [SYS]> commit;
Query OK, 0 rows affected (0.002 sec)

obclient [SYS]> create index dbmt.idx_tablea on dbmt.tablea(id);
Query OK, 0 rows affected (0.892 sec)

• 查看 dba_objects 视图

obclient [SYS]> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
Query OK, 0 rows affected (0.001 sec)

obclient [SYS]> select owner,object_name,subobject_name,object_type,created,last_ddl_time from dba_objects where object_name='TABLEA';
+-------+-------------+----------------+-----------------+---------------------+---------------------+
| OWNER | OBJECT_NAME | SUBOBJECT_NAME | OBJECT_TYPE     | CREATED             | LAST_DDL_TIME       |
+-------+-------------+----------------+-----------------+---------------------+---------------------+
| DBMT  | TABLEA      | NULL           | TABLE           | 2023-12-13 04:20:23 | 2023-12-13 04:20:42 |
| DBMT  | TABLEA      | P1             | TABLE PARTITION | 2023-12-13 04:20:23 | 2023-12-13 04:20:23 |
| DBMT  | TABLEA      | P2             | TABLE PARTITION | 2023-12-13 04:20:23 | 2023-12-13 04:20:23 |
+-------+-------------+----------------+-----------------+---------------------+---------------------+
3 rows in set (0.021 sec)

• 对分区执行truncate partition操作

obclient [SYS]> alter table dbmt.tablea truncate partition p1;
Query OK, 0 rows affected (0.098 sec)

obclient [SYS]> select owner,object_name,subobject_name,object_type,created,last_ddl_time from dba_objects where object_name='TABLEA';
+-------+-------------+----------------+-----------------+---------------------+---------------------+
| OWNER | OBJECT_NAME | SUBOBJECT_NAME | OBJECT_TYPE     | CREATED             | LAST_DDL_TIME       |
+-------+-------------+----------------+-----------------+---------------------+---------------------+
| DBMT  | TABLEA      | NULL           | TABLE           | 2023-12-13 04:20:23 | 2023-12-13 04:21:28 |
| DBMT  | TABLEA      | P2             | TABLE PARTITION | 2023-12-13 04:20:23 | 2023-12-13 04:20:23 |
| DBMT  | TABLEA      | P1             | TABLE PARTITION | 2023-12-13 04:21:28 | 2023-12-13 04:21:28 |
+-------+-------------+----------------+-----------------+---------------------+---------------------+
3 rows in set (0.022 sec)

P1分区的 LAST_DDL_TIME 字段时间被更新是正常的，但是 CREATED 字段的时间也被更新了，这是不符合逻辑的，失去了 CREATED 字段的含义。

• 在Oracle上执行相同的测试，CREATED字段是不会被更新的
  

• 对分区执行add partition操作

obclient [SYS]> alter table dbmt.tablea add partition P3 values less than(300);
Query OK, 0 rows affected (0.077 sec)

obclient [SYS]> select owner,object_name,subobject_name,object_type,created,last_ddl_time from dba_objects where object_name='TABLEA';
+-------+-------------+----------------+-----------------+---------------------+---------------------+
| OWNER | OBJECT_NAME | SUBOBJECT_NAME | OBJECT_TYPE     | CREATED             | LAST_DDL_TIME       |
+-------+-------------+----------------+-----------------+---------------------+---------------------+
| DBMT  | TABLEA      | NULL           | TABLE           | 2023-12-13 04:22:07 | 2023-12-13 04:22:07 |
| DBMT  | TABLEA      | P2             | TABLE PARTITION | 2023-12-13 04:20:23 | 2023-12-13 04:20:23 |
| DBMT  | TABLEA      | P1             | TABLE PARTITION | 2023-12-13 04:21:28 | 2023-12-13 04:21:28 |
| DBMT  | TABLEA      | P3             | TABLE PARTITION | 2023-12-13 04:22:07 | 2023-12-13 04:22:07 |
+-------+-------------+----------------+-----------------+---------------------+---------------------+
4 rows in set (0.021 sec)

在Oceanbase中，add partition 更新新添加分区的CREATED和LAST_DDL_TIME字段是预期行为，和Oracle的行为一样，但是Oceanbase上同时还更新了OBJECT_TYPE=TABLE的CREATED和LAST_DDL_TIME字段，不符合逻辑，而Oracle不会更新。

• 对分区执行drop partition操作

obclient [SYS]> alter table dbmt.tablea drop partition P3 update global indexes;
Query OK, 0 rows affected (0.038 sec)

obclient [SYS]> select owner,object_name,subobject_name,object_type,created,last_ddl_time from dba_objects where object_name='TABLEA';
+-------+-------------+----------------+-----------------+---------------------+---------------------+
| OWNER | OBJECT_NAME | SUBOBJECT_NAME | OBJECT_TYPE     | CREATED             | LAST_DDL_TIME       |
+-------+-------------+----------------+-----------------+---------------------+---------------------+
| DBMT  | TABLEA      | NULL           | TABLE           | 2023-12-13 04:23:26 | 2023-12-13 04:23:26 |
| DBMT  | TABLEA      | P2             | TABLE PARTITION | 2023-12-13 04:20:23 | 2023-12-13 04:20:23 |
| DBMT  | TABLEA      | P1             | TABLE PARTITION | 2023-12-13 04:21:28 | 2023-12-13 04:21:28 |
+-------+-------------+----------------+-----------------+---------------------+---------------------+
3 rows in set (0.021 sec)

在Oceanbase中，drop partition 更新OBJECT_TYPE=TABLE的LAST_DDL_TIME字段是预期行为，和Oracle的行为一样，但是Oceanbase上同时还更新了OBJECT_TYPE=TABLE的CREATED字段，不符合逻辑，而Oracle不会更新。

• 对整个分区表执行truncate操作

obclient [SYS]> truncate table dbmt.tablea;
Query OK, 0 rows affected (0.120 sec)

obclient [SYS]> select owner,object_name,subobject_name,object_type,created,last_ddl_time from dba_objects where object_name='TABLEA';
+-------+-------------+----------------+-----------------+---------------------+---------------------+
| OWNER | OBJECT_NAME | SUBOBJECT_NAME | OBJECT_TYPE     | CREATED             | LAST_DDL_TIME       |
+-------+-------------+----------------+-----------------+---------------------+---------------------+
| DBMT  | TABLEA      | NULL           | TABLE           | 2023-12-13 04:47:15 | 2023-12-13 04:47:15 |
| DBMT  | TABLEA      | P1             | TABLE PARTITION | 2023-12-13 04:47:15 | 2023-12-13 04:47:15 |
| DBMT  | TABLEA      | P2             | TABLE PARTITION | 2023-12-13 04:47:15 | 2023-12-13 04:47:15 |
+-------+-------------+----------------+-----------------+---------------------+---------------------+
3 rows in set (0.022 sec)

在Oceanbase中，对整个分区表执行truncate操作会更新所有CREATED和LAST_DDL_TIME的字段时间，不符合逻辑，而Oracle只会更新LAST_DDL_TIME的字段时间。