本文介绍 OceanBase 数据库 V4.2.1 以下版本中 DBMS_SCHEDULER.CREATE_JOB 可以创建多个同名 JOB 且无法删除的原因和解决方法。
问题描述
在 OceanBase 数据库 V4.x 及以下版本，DBMS_SCHEDULER.CREATE_JOB 存在缺陷，可以创建多个同名 JOB，导致使用 DBMS_SCHEDULER.DROP_JOB 或者 DBMS_SCHEDULER.DISABLE 操作 JOB 时，均报错。
ORA-01422: exact fetch returns more than requested number of rows at package body
适用版本
OceanBase 数据库 V3.x 和 V4.x 版本。
问题原因
DBMS_SCHEDULER.CREATE_JOB 缺陷，创建时未过滤拦截同名，导致可以创建多个同名 JOB。
解决方法
解决方法: 升级至问题已修复版本。目前已修复该问题的版本包括 OceanBase 数据库 4.2.1 GA (oceanbase-4.2.1.0-100000182023092722) 及之后版本。
临时解决办法：
登录 SYS 租户，切换到业务租户，删除
__all_tenant_scheduler_job记录，步骤如下。[admin@hostname xxxxx]$ obclient -hxx.xx.xx.xx -Pxx -uroot@sys -Dxxx -pxxxxobclient > ALTER SYSTEM CHANGE TENANT xx01;obclient > select * from __all_tenant_scheduler_job;输出内容如下:
+----------------------------+----------------------------+-----------+------------------------------+-----+--------+--------+--------+----------------------------+-----------+----------------------------+-----------+---------------------------+----------+------+----------------------------+--------+---------+--------+-----------------+----------------------+-----------+--------------+------------------+----------------------------+--------------------+----------------------------+---------------------------+----------------------------+-------------------+---------+-----------+-----------+-----------+-------------+-------------------+------------------+----------+-----------------+------------------+-------------+ | gmt_create | gmt_modified | tenant_id | job_name | job | lowner | powner | cowner | last_date | this_date | next_date | total | interval# | failures | flag | what | nlsenv | charenv | field1 | scheduler_flags | exec_env | job_style | program_name | job_type | job_action | number_of_argument | start_date | repeat_interval | end_date | job_class | enabled | auto_drop | state | run_count | retry_count | last_run_duration | max_run_duration | comments | credential_name | destination_name | interval_ts | +----------------------------+----------------------------+-----------+------------------------------+-----+--------+--------+--------+----------------------------+-----------+----------------------------+-----------+---------------------------+----------+------+----------------------------+--------+---------+--------+-----------------+----------------------+-----------+--------------+------------------+----------------------------+--------------------+----------------------------+---------------------------+----------------------------+-------------------+---------+-----------+-----------+-----------+-------------+-------------------+------------------+----------+-----------------+------------------+-------------+ | 2023-10-08 16:09:56.893183 | 2023-10-09 15:05:37.029193 | 0 | JOB$_1 | 1 | SYS | SYS | SYS | 2023-10-09 15:05:37.009687 | NULL | 2023-10-09 15:05:45.000000 | 202053183 | FREQ=SECONDLY; INTERVAL=8 | 0 | 0 | JOB_UTILS.insert_date | NULL | NULL | NULL | 0 | 2151677954,45,46,46, | REGULER | NULL | STORED_PROCEDURE | JOB_UTILS.insert_date | 5 | 2023-10-08 16:09:56.000000 | FREQ=SECONDLY; INTERVAL=8 | 2024-01-16 16:09:56.000000 | DEFAULT_JOB_CLASS | 1 | 1 | SCHEDULED | NULL | NULL | NULL | 0 | test | null | null | 8000000 | | 2023-10-09 12:35:23.612728 | 2023-10-09 12:37:23.018619 | 0 | J_DELETE_WORK_ORDER_HIS_DATA | 3 | SYS | SYS | SYS | 2023-10-09 12:37:23.010217 | NULL | 4000-01-01 00:00:00.000000 | 138833 | FREQ=SECONDLY; INTERVAL=8 | 16 | 1 | DELETE_WORK_ORDER_HIS_DATA | NULL | NULL | NULL | 0 | 2151677954,45,46,46, | REGULER | NULL | STORED_PROCEDURE | DELETE_WORK_ORDER_HIS_DATA | 0 | 2023-10-09 12:35:23.000000 | FREQ=SECONDLY; INTERVAL=8 | 2024-01-17 12:35:23.000000 | DEFAULT_JOB_CLASS | 1 | 0 | BROKEN | NULL | NULL | NULL | 0 | test | null | null | 8000000 | | 2023-10-09 12:35:27.271747 | 2023-10-09 12:37:28.017067 | 0 | J_DELETE_WORK_ORDER_HIS_DATA | 4 | SYS | SYS | SYS | 2023-10-09 12:37:28.009429 | NULL | 4000-01-01 00:00:00.000000 | 136957 | FREQ=SECONDLY; INTERVAL=8 | 16 | 1 | DELETE_WORK_ORDER_HIS_DATA | NULL | NULL | NULL | 0 | 2151677954,45,46,46, | REGULER | NULL | STORED_PROCEDURE | DELETE_WORK_ORDER_HIS_DATA | 0 | 2023-10-09 12:35:27.000000 | FREQ=SECONDLY; INTERVAL=8 | 2024-01-17 12:35:27.000000 | DEFAULT_JOB_CLASS | 1 | 0 | BROKEN | NULL | NULL | NULL | 0 | test | null | null | 8000000 | | 2023-10-09 12:35:29.802080 | 2023-10-09 12:37:33.019744 | 0 | J_DELETE_WORK_ORDER_HIS_DATA | 5 | SYS | SYS | SYS | 2023-10-09 12:37:33.012028 | NULL | 4000-01-01 00:00:00.000000 | 142953 | FREQ=SECONDLY; INTERVAL=8 | 16 | 1 | DELETE_WORK_ORDER_HIS_DATA | NULL | NULL | NULL | 0 | 2151677954,45,46,46, | REGULER | NULL | STORED_PROCEDURE | DELETE_WORK_ORDER_HIS_DATA | 0 | 2023-10-09 12:35:29.000000 | FREQ=SECONDLY; INTERVAL=8 | 2024-01-17 12:35:29.000000 | DEFAULT_JOB_CLASS | 1 | 0 | BROKEN | NULL | NULL | NULL | 0 | test | null | null | 8000000 | | 2023-10-08 16:09:56.893183 | 2023-10-09 12:35:29.802698 | 0 | __dummy_guard | 6 | SYS | SYS | SYS | NULL | NULL | 2023-10-08 16:09:56.896587 | 0 | null | 0 | 0 | | | NULL | NULL | 0 | | NULL | NULL | | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +----------------------------+----------------------------+-----------+------------------------------+-----+--------+--------+--------+----------------------------+-----------+----------------------------+-----------+---------------------------+----------+------+----------------------------+--------+---------+--------+-----------------+----------------------+-----------+--------------+------------------+----------------------------+--------------------+----------------------------+---------------------------+----------------------------+-------------------+---------+-----------+-----------+-----------+-------------+-------------------+------------------+----------+-----------------+------------------+-------------+ 5 rows in set (0.011 sec)obclient > delete from __all_tenant_scheduler_job where job in (3,4,5);obclient > commit;业务租户确认。
[admin@hostname xxxxx]$ obclient -hxx.xx.xx.xx -Pxx01 -uSYS@xxx -DSYS -p****obclient > SELECT JOB_NAME,enabled,NEXT_RUN_DATE,state,last_start_date FROM DBA_SCHEDULER_JOBS;
