DBMS_SCHEDULER.CREATE_JOB 创建多个同名 JOB 且无法删除

本文介绍 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) 及之后版本。

• 临时解决办法：

  1. 登录 SYS 租户，切换到业务租户，删除 __all_tenant_scheduler_job 记录，步骤如下。

     [admin@hostname xxxxx]$ obclient -hxx.xx.xx.xx -Pxx -uroot@sys -Dxxx -pxxxx
     

     obclient > 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;
     

  2. 业务租户确认。

     [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;