问题现象
OceanBase 数据库 V4.x 版本 Oracle 租户修改自动收集统计信息时间无效。
关键信息
如下直接执行虽然不会报错，但是在 DBA_SCHEDULER_JOBS 表中，NEXT_RUN_DATE 并不会生效。
obclient> call dbms_scheduler.set_attribute(
'FRIDAY_WINDOW',
'NEXT_DATE',
TO_DATE('27-DEC-2024 19:00:00', 'DD-MON-YYYY HH24:MI:SS'));
Query OK, 0 rows affected (0.01 sec)
obclient> select JOB_NAME, NEXT_RUN_DATE from DBA_SCHEDULER_JOBS;
+---------------------------+-------------------------------------+
| JOB_NAME | NEXT_RUN_DATE |
+---------------------------+-------------------------------------+
| FRIDAY_WINDOW | 27-DEC-24 12.00.00.000000 AM +08:00 |
| jcz | 01-JAN-00 12.00.00.000000 AM +08:00 |
| JOB$_1125899908117938 | 26-DEC-24 11.28.16.000000 AM +08:00 |
| MONDAY_WINDOW | 30-DEC-24 10.00.00.000000 PM +08:00 |
| OPT_STATS_HISTORY_MANAGER | 27-DEC-24 10.28.49.246179 AM +08:00 |
| SATURDAY_WINDOW | 28-DEC-24 06.00.00.000000 AM +08:00 |
| SUNDAY_WINDOW | 29-DEC-24 06.00.00.000000 AM +08:00 |
| test123 | 01-JAN-00 12.00.00.000000 AM +08:00 |
| THURSDAY_WINDOW | 02-JAN-25 05.00.00.000000 AM +08:00 |
| TUESDAY_WINDOW | 31-DEC-24 10.00.00.000000 PM +08:00 |
| WEDNESDAY_WINDOW | 01-JAN-25 12.00.00.000000 AM +08:00 |
+---------------------------+-------------------------------------+
11 rows in set (0.01 sec)
需要先手动设置 NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT、NLS_TIMESTAMP_TZ_FORMAT 如下。
obclient> SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
Query OK, 0 rows affected (0.00 sec)
obclient> SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF';
Query OK, 0 rows affected (0.00 sec)
obclient> SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
Query OK, 0 rows affected (0.00 sec)
obclient> call dbms_scheduler.set_attribute(
'FRIDAY_WINDOW',
'NEXT_DATE',
TO_DATE('27-DEC-2024 19:00:00', 'DD-MON-YYYY HH24:MI:SS'));
Query OK, 0 rows affected (0.01 sec)
obclient> select JOB_NAME, NEXT_RUN_DATE from DBA_SCHEDULER_JOBS;
+---------------------------+------------------------------------+
| JOB_NAME | NEXT_RUN_DATE |
+---------------------------+------------------------------------+
| FRIDAY_WINDOW | 2024-12-27 19:00:00.000000 +08:00 |
| jcz | 4000-01-01 00:00:00.000000 +08:00 |
| JOB$_1125899908117938 | 2024-12-26 11:28:40.000000 +08:00 |
| MONDAY_WINDOW | 2024-12-30 22:00:00.000000 +08:00 |
| OPT_STATS_HISTORY_MANAGER | 2024-12-27 10:28:49.246179 +08:00 |
| SATURDAY_WINDOW | 2024-12-28 06:00:00.000000 +08:00 |
| SUNDAY_WINDOW | 2024-12-29 06:00:00.000000 +08:00 |
| test123 | 4000-01-01 00:00:00.000000 +08:00 |
| THURSDAY_WINDOW | 2025-01-02 05:00:00.000000 +08:00 |
| TUESDAY_WINDOW | 2024-12-31 22:00:00.000000 +08:00 |
| WEDNESDAY_WINDOW | 2025-01-01 00:00:00.000000 +08:00 |
+---------------------------+------------------------------------+
11 rows in set (0.00 sec)
问题原因
Oracle 租户需要先执行以下命令后再设置时间。
SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF';
SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
问题的风险及影响
设置自动收集统计信息时间失败。
影响租户
影响 OceanBase 数据库中的 Oracle 租户，对于 SYS 租户和 MySQL 租户无影响。
适用版本
OceanBase 数据库 V4.x 版本。
解决方法
需要先手动设置 NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT、NLS_TIMESTAMP_TZ_FORMAT。
