--- title: DBMS_SCHEDULER.CREATE_JOB 创建多个同名 JOB 且无法删除-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 DBMS_SCHEDULER.CREATE_JOB 创建多个同名 JOB 且无法删除相关的常见问题和使用技巧，帮助您快速解决 DBMS_SCHEDULER.CREATE_JOB 创建多个同名 JOB 且无法删除的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # DBMS_SCHEDULER.CREATE_JOB 创建多个同名 JOB 且无法删除 更新时间：2024-03-13 09:27 适用版本： V3.2.x、V4.0.x、V4.1.x、V4.2.x 内容类型：TechNote 本文介绍 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 时，均报错。 ```shell 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` 记录，步骤如下。 ```shell [admin@hostname xxxxx]$ obclient -hxx.xx.xx.xx -Pxx -uroot@sys -Dxxx -pxxxx ``` ```shell obclient > ALTER SYSTEM CHANGE TENANT xx01; ``` ```shell obclient > select * from __all_tenant_scheduler_job; ``` 输出内容如下: ```shell +----------------------------+----------------------------+-----------+------------------------------+-----+--------+--------+--------+----------------------------+-----------+----------------------------+-----------+---------------------------+----------+------+----------------------------+--------+---------+--------+-----------------+----------------------+-----------+--------------+------------------+----------------------------+--------------------+----------------------------+---------------------------+----------------------------+-------------------+---------+-----------+-----------+-----------+-------------+-------------------+------------------+----------+-----------------+------------------+-------------+ | 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) ``` ```shell obclient > delete from __all_tenant_scheduler_job where job in (3,4,5); ``` ```shell obclient > commit; ``` 2. 业务租户确认。 ```shell [admin@hostname xxxxx]$ obclient -hxx.xx.xx.xx -Pxx01 -uSYS@xxx -DSYS -p**** ``` ```shell obclient > SELECT JOB_NAME,enabled,NEXT_RUN_DATE,state,last_start_date FROM DBA_SCHEDULER_JOBS; ``` Previous [分区维护后，DBA_TAB_SUBPARTITIONS 内容没有更新](https://www.oceanbase.com/knowledge-base/oceanbase-database-20000045549) Next [sql_audit 里的 elapsed time 是真正的业务感知到的 SQL 耗时吗？](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000639278) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线