前言
OceanBase在不同版本均提供了DBMS_SCHEDULER计划认读调度的功能，但各版本存在支持度差异，以下是基于业务场景对4.2.5及4.4.2版本做DBMS_SCHEDULER的兼容性测试。
构建用于测试DBMS_SCHEDULER功能的库表、存储过程（这里用了AI工具辅助生成）
-- 提供给AI的 DBMS_SCHEDULER 模板
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'OB_SCHEDULER_SYNTAX_TEST',
job_type => 'STORED_PROCEDURE',
job_action => 'PRC_OB_SCHEDULER_SYNTAX_TEST',
repeat_interval => '',
start_date => sysdate,
enabled => TRUE,
comments => 'OceanBase Scheduler repeat_interval 参数语法兼容性测试'
);
END;
/
-- 1. 创建测试结果记录表
CREATE TABLE OB_SCHEDULER_TEST_LOG (
test_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
syntax_text VARCHAR2(255),
is_supported VARCHAR2(10),
next_run_time TIMESTAMP,
error_msg VARCHAR2(4000),
test_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
comments VARCHAR2(255)
);
-- 2. 创建测试用的存储过程，将不同的语法列表放入存储过程循环构建任务
CREATE OR REPLACE PROCEDURE PRC_OB_SCHEDULER_REAL_TEST AS
TYPE t_syntax IS TABLE OF VARCHAR2(255);
v_intervals t_syntax := t_syntax(
-- FREQ单语法语法测试
'FREQ=YEARLY;INTERVAL=1', -- 年
'FREQ=MONTHLY;INTERVAL=1', -- 月
'FREQ=WEEKLY;INTERVAL=1', -- 周
'FREQ=DAILY;INTERVAL=1', -- 日
'FREQ=HOURLY;INTERVAL=1', -- 小时
'FREQ=MINUTELY;INTERVAL=5', -- 分钟
'FREQ=SECONDLY;INTERVAL=30',-- 秒
-- 组合精确时间语法测试
'FREQ=YEARLY;BYYEARDAY=10',
'FREQ=MONTHLY;BYMONTHDAY=10',
'FREQ=MONTHLY;BYMONTHDAY=10',
'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
'FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1;BYHOUR=01;BYMINUTE=01;BYSECOND=01',
'FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1;BYHOUR=01;BYMINUTE=01',
'FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1;BYHOUR=01',
'FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1',
'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
-- 真实业务JOB 去重后的语法类型测试
'FREQ=MINUTELY;INTERVAL=5',
'FREQ=HOURLY;INTERVAL=12',
'FREQ=HOURLY;INTERVAL=1',
'FREQ=MINUTELY;INTERVAL=5',
'FREQ=HOURLY;INTERVAL=2',
'FREQ=DAILY;BYHOUR=01;BYMINUTE=01;BYSECOND=01',
'FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1;BYHOUR=01;BYMINUTE=01;BYSECOND=01',
'FREQ=DAILY;BYHOUR=0',
'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=1;BYMINUTE=0;BYSECOND=0',
'FREQ=MINUTELY;INTERVAL=1',
'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
'FREQ=MINUTELY;INTERVAL=10',
'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0',
'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
'FREQ=MONTHLY;BYMONTHDAY=2;BYHOUR=1;BYMINUTE=0;BYSECOND=0',
'FREQ=MINUTELY;INTERVAL=1',
'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
'FREQ=MINUTELY;INTERVAL=10',
'FREQ=DAILY;INTERVAL=1;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=0;BYMINUTE=1;BYSECOND=0',
'FREQ=MINUTELY;INTERVAL=5',
'FREQ=DAILY;BYHOUR=0;BYMINUTE=30;BYSECOND=0',
'FREQ=DAILY;BYHOUR=0;BYMINUTE=30;BYSECOND=0',
'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
'FREQ=HOURLY;INTERVAL=1',
'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=20;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;INTERVAL=1;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
'FREQ=WEEKLY;BYDAY=MON;BYHOUR=1;BYMINUTE=0;BYSECOND=0',
'FREQ=YEARLY;BYMONTH=1;BYMONTHDAY=1;BYHOUR=3;BYMINUTE=0;BYSECOND=0',
'FREQ=HOURLY;INTERVAL=1',
'FREQ=HOURLY;INTERVAL=1',
'FREQ=DAILY;BYHOUR=17;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=23;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=22;BYMINUTE=0;BYSECOND=0',
'FREQ=MINUTELY;INTERVAL=30',
'FREQ=MINUTELY;INTERVAL=20',
'FREQ=WEEKLY;INTERVAL=1',
'FREQ=MINUTELY;INTERVAL=15',
'FREQ=DAILY;BYHOUR=22;BYMINUTE=0;BYSECOND=0',
'FREQ=MINUTELY;INTERVAL=10',
'FREQ=DAILY;BYHOUR=21;BYMINUTE=30;BYSECOND=0',
'FREQ=DAILY;BYHOUR=21;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=5;BYMINUTE=30;BYSECOND=0',
'FREQ=DAILY;BYHOUR=5;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=3;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;INTERVAL=1',
'FREQ=DAILY;BYHOUR=17;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=22;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0',
'FREQ=HOURLY;INTERVAL=1',
'FREQ=HOURLY;INTERVAL=1',
'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;INTERVAL=1;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
'FREQ=HOURLY;BYMINUTE=0;BYSECOND=0',
'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=5;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=1;BYMINUTE=0;BYSECOND=0',
'FREQ=DAILY;BYHOUR=22;BYMINUTE=0;BYSECOND=0',
'FREQ=MONTHLY;BYMONTHDAY=2;BYHOUR=4;BYMINUTE=10;BYSECOND=0',
'FREQ=DAILY;BYHOUR=7;BYMINUTE=0;BYSECOND=0',
'FREQ=SECONDLY;INTERVAL=600',
'FREQ=DAILY;BYHOUR=6;BYMINUTE=0;BYSECOND=0',
'FREQ=MONTHLY;BYMONTHDAY=2;BYHOUR=2;BYMINUTE=0;BYSECOND=0'
);
v_job_name VARCHAR2(128);
v_err_msg VARCHAR2(4000);
BEGIN
FOR i IN 1..v_intervals.COUNT LOOP
v_job_name := 'TEMP_TEST_JOB_' || i;
BEGIN
-- 1. 尝试创建 Job (不启动，只验证语法)
DBMS_SCHEDULER.CREATE_JOB (
job_name => v_job_name,
job_type => 'PLSQL_BLOCK',
job_action => 'NULL;',
repeat_interval => v_intervals(i),
enabled => FALSE -- 关键：不启用，仅验证语法合法性
);
-- 2. 语法合法，记录成功并立即清理
INSERT INTO OB_SCHEDULER_TEST_LOG (syntax_text, is_supported, error_msg)
VALUES (v_intervals(i), 'YES', 'VALIDATED BY DBMS_SCHEDULER.CREATE_JOB');
DBMS_SCHEDULER.DROP_JOB(v_job_name);
EXCEPTION
WHEN OTHERS THEN
-- 3. 语法非法，捕获 OB 返回的错误
v_err_msg := SQLERRM;
INSERT INTO OB_SCHEDULER_TEST_LOG (syntax_text, is_supported, error_msg)
VALUES (v_intervals(i), 'NO', v_err_msg);
END;
COMMIT;
END LOOP;
END;
/
-- 3. 运行测试
CALL PRC_OB_SCHEDULER_REAL_TEST();
测试结果：4.2.5.7版本测试，结论：只支持FREQ及INTERVAL参数
-------------------------------- 测试结果 --------------------------------
obclient(SYS@oboracle)[SYS]> SELECT * FROM OB_SCHEDULER_TEST_LOG ORDER BY test_id;
+---------+------------------------------------------------------------------------+--------------+---------------+-------------------------------------------------------------------------------+----------------------------+----------+
| TEST_ID | SYNTAX_TEXT | IS_SUPPORTED | NEXT_RUN_TIME | ERROR_MSG | TEST_DATE | COMMENTS |
+---------+------------------------------------------------------------------------+--------------+---------------+-------------------------------------------------------------------------------+----------------------------+----------+
| 1 | FREQ=YEARLY;INTERVAL=1 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:02:05.680500 | 4.2.5.7 |
| 2 | FREQ=MONTHLY;INTERVAL=1 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:02:05.693957 | 4.2.5.7 |
| 3 | FREQ=WEEKLY;INTERVAL=1 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:02:05.701447 | 4.2.5.7 |
| 4 | FREQ=DAILY;INTERVAL=1 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:02:05.707892 | 4.2.5.7 |
| 5 | FREQ=HOURLY;INTERVAL=1 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:02:05.714328 | 4.2.5.7 |
| 6 | FREQ=MINUTELY;INTERVAL=5 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:02:05.720746 | 4.2.5.7 |
| 7 | FREQ=SECONDLY;INTERVAL=30 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:02:05.727191 | 4.2.5.7 |
| 8 | FREQ=YEARLY;BYYEARDAY=10 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:02:05.734903 | 4.2.5.7 |
| 9 | FREQ=MONTHLY;BYMONTHDAY=10 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:02:05.742410 | 4.2.5.7 |
| 10 | FREQ=MONTHLY;BYMONTHDAY=10 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:02:05.748872 | 4.2.5.7 |
| 11 | FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0 | NO | NULL | ORA-00600: internal error code, arguments: -4249, Data truncated for argument | 2026-03-20 11:02:05.755105 | 4.2.5.7 |
| 12 | FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1;BYHOUR=01;BYMINUTE=01;BYSECOND=01 | NO | NULL | ORA-00600: internal error code, arguments: -4249, Data truncated for argument | 2026-03-20 11:02:05.760668 | 4.2.5.7 |
| 13 | FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1;BYHOUR=01;BYMINUTE=01 | NO | NULL | ORA-00600: internal error code, arguments: -4249, Data truncated for argument | 2026-03-20 11:02:05.761740 | 4.2.5.7 |
| 14 | FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1;BYHOUR=01 | NO | NULL | ORA-00600: internal error code, arguments: -4249, Data truncated for argument | 2026-03-20 11:02:05.762804 | 4.2.5.7 |
| 15 | FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1 | NO | NULL | ORA-00600: internal error code, arguments: -4249, Data truncated for argument | 2026-03-20 11:02:05.764954 | 4.2.5.7 |
+---------+------------------------------------------------------------------------+--------------+---------------+-------------------------------------------------------------------------------+----------------------------+----------+
15 rows in set (0.001 sec)
4.4.2版本测试，结论：额外支持BYMONTHDAY、BYHOUR、BYMINUTE、BYSECOND等参数
obclient(SYS@oboracle)[SYS]> SELECT * FROM OB_SCHEDULER_TEST_LOG ORDER BY test_id;
+---------+------------------------------------------------------------------------+--------------+---------------+-------------------------------------------------------------------------------+----------------------------+----------+
| TEST_ID | SYNTAX_TEXT | IS_SUPPORTED | NEXT_RUN_TIME | ERROR_MSG | TEST_DATE | COMMENTS |
+---------+------------------------------------------------------------------------+--------------+---------------+-------------------------------------------------------------------------------+----------------------------+----------+
| 41 | FREQ=YEARLY;INTERVAL=1 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.087151 | 4.4.2 |
| 42 | FREQ=MONTHLY;INTERVAL=1 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.106001 | 4.4.2 |
| 43 | FREQ=WEEKLY;INTERVAL=1 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.112395 | 4.4.2 |
| 44 | FREQ=DAILY;INTERVAL=1 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.117698 | 4.4.2 |
| 45 | FREQ=HOURLY;INTERVAL=1 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.123069 | 4.4.2 |
| 46 | FREQ=MINUTELY;INTERVAL=5 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.128489 | 4.4.2 |
| 47 | FREQ=SECONDLY;INTERVAL=30 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.132725 | 4.4.2 |
| 48 | FREQ=YEARLY;BYYEARDAY=10 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.138064 | 4.4.2 |
| 49 | FREQ=MONTHLY;BYMONTHDAY=10 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.143416 | 4.4.2 |
| 50 | FREQ=MONTHLY;BYMONTHDAY=10 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.148755 | 4.4.2 |
| 51 | FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.154112 | 4.4.2 |
| 52 | FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1;BYHOUR=01;BYMINUTE=01;BYSECOND=01 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.162666 | 4.4.2 |
| 53 | FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1;BYHOUR=01;BYMINUTE=01 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.167962 | 4.4.2 |
| 54 | FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1;BYHOUR=01 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.173451 | 4.4.2 |
| 55 | FREQ=MONTHLY;INTERVAL=1;BYMONTHDAY=1 | YES | NULL | VALIDATED BY DBMS_SCHEDULER.CREATE_JOB | 2026-03-20 11:22:54.178798 | 4.4.2 |
+---------+------------------------------------------------------------------------+--------------+---------------+-------------------------------------------------------------------------------+----------------------------+----------+
测试总结：
- 4.2.1及4.2.5版本已经支持DBMS_SCHEDULER调度任务，但仅支持只支持FREQ及INTERVAL参数，粒度比较粗，不一定满足实际业务场景需求。
- 4.3.5及4.4.2版本额外支持BYMONTHDAY、BYHOUR、BYMINUTE、BYSECOND等参数，已经几乎覆盖绝大部分真实业务场景需求。
补充测试：在4.4.2版本测试基于真实环境的Oracle计划任务转为OceanBase Oracle租户的语法兼容测试
测试结论：Oracle中所有计划任务执行粒度在OB侧均支持（备注：只代表我验证的业务库均支持，不代表所有Oracle的计划任务都支持，具体需以实际验证为准）