DBMS_SCHEDULER 系统包用于管理定时任务。它提供了一组函数和过程，用户可以从 PL 程序调用这些函数和过程。 本文主要介绍对于使用 DBMS_SCHEDULER 程序包创建并自动执行的调度任务，如何查看这些调度任务当前的实时运行情况（比如是否正在运行、运行过程中的 SESSION ID 等），以及已经执行完成的调度任务详情。
详细说明
OceanBase 数据库目前提供的与 DBMS_SCHEDULER 系统包相关的标准视图与虚拟表主要有以下几个：
DBA_SCHEDULER_JOBS： 该视图从 V3.2.3 版本开始引入，用于展示当前用户可访问的调度程序作业信息。目前所有租户（包括系统 sys 租户）下均存在该视图，关于该视图的详细介绍，可以参见官网：DBA_SCHEDULER_JOBS。
DBA_SCHEDULER_JOB_RUN_DETAILS： 该视图从 V4.2.1 版本开始引入，用于展示当前租户中所有已经运行结束的调度程序作业的详情。目前所有租户（包括系统 sys 租户）下均存在该视图，关于该视图的详细介绍，可以参见官网：DBA_SCHEDULER_JOB_RUN_DETAILS。
CDB_SCHEDULER_JOB_RUN_DETAILS： 该视图仅系统 sys 租户下存在，用于展示所有租户下所有调度程序作业的信息。关于该视图的详细介绍，可以参见官网：oceanbase.CDB_SCHEDULER_JOB_RUN_DETAILS。
从 OceanBase 数据库 V4.2.5 BP5 版本开始，在系统 sys 租户下提供一张虚拟表
oceanbase.__all_virtual_tenant_scheduler_running_job，用于展示正在运行中的 JOB 对应的 SESSION ID 等信息，此表字段兼容原生 Oracle 的 ALL_SCHEDULER_RUNNING_JOBS。 （说明：目前该虚拟表在普通业务租户下无任何对应的视图。）
测试如下：
创建张测试表。
[root@ob55 ~]# obclient -h127.0.0.1 -P2881 -usys@oraclet -pxxx -A -c Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221653582 Server version: OceanBase 4.3.5.4 (r104000052025090918-eb47078c7c81380a18394e5f5160ab65d5bea812) (Built Sep 9 2025 18:46:08) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient [SYS]> select @@version_comment from dual; +---------------------------------------------------------------------------------------------------------------+ | @@VERSION_COMMENT | +---------------------------------------------------------------------------------------------------------------+ | OceanBase 4.3.5.4 (r104000052025090918-eb47078c7c81380a18394e5f5160ab65d5bea812) (Built Sep 9 2025 18:46:08) | +---------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec) obclient [SYS]> CREATE TABLE jobtab(col1 timestamp); Query OK, 0 rows affected (0.150 sec)创建存储过程，在调度程序作业中使用。
-- create a stored procedure to be used in scheduler job -- it will insert a line into the test table obclient [SYS]> CREATE OR REPLACE PACKAGE JOB_UTILS IS PROCEDURE insert_time; END / Query OK, 0 rows affected (0.054 sec) obclient [SYS]> CREATE OR REPLACE PACKAGE BODY JOB_UTILS IS PROCEDURE insert_time IS BEGIN DBMS_LOCK.SLEEP(60); -- sleep 60s INSERT INTO jobtab VALUES(systimestamp); COMMIT; END; END / Query OK, 0 rows affected (0.108 sec)创建名为 my_job 的调度作业。
obclient [SYS]> BEGIN DBMS_SCHEDULER.CREATE_JOB(job_name => 'my_job', job_type => 'STORED_PROCEDURE', job_action => 'JOB_UTILS.insert_time', number_of_argument => 0, start_date => sysdate, -- start now at once repeat_interval => 'FREQ=SECONDLY; INTERVAL=300', -- run once every 300s end_date => sysdate + 100, -- end_date - start_date = 100 days job_class => 'DEFAULT_JOB_CLASS', enabled => true, auto_drop => true); COMMIT; END / Query OK, 1 row affected (1.076 sec)查看调度作业 my_job 详细属性。
obclient [SYS]> select job_name,job_action,comments,start_date,end_date from dba_scheduler_jobs where job_name='my_job'\G *************************** 1. row *************************** JOB_NAME: my_job JOB_ACTION: JOB_UTILS.insert_time COMMENTS: NULL START_DATE: 25-OCT-25 01.43.22.000000 PM +08:00 END_DATE: 02-FEB-26 01.43.22.000000 PM +08:00 1 row in set (0.006 sec)查看调度任务 my_job 的当前运行情况。
[root@ob55 ~]# mysql -h127.0.0.1 -P2881 -uroot@sys -pxxx -A -c oceanbase Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221503455 Server version: 5.7.25 OceanBase 4.3.5.4 (r104000052025090918-eb47078c7c81380a18394e5f5160ab65d5bea812) (Built Sep 9 2025 18:46:08) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]> show tenant; +---------------------+ | Current_tenant_name | +---------------------+ | sys | +---------------------+ 1 row in set (0.002 sec) MySQL [oceanbase]> select * from oceanbase.__all_virtual_tenant_scheduler_running_job\G *************************** 1. row *************************** svr_ip: xxx.xxx.xxx.xxx svr_port: 2882 tenant_id: 1012 owner: SYS job_name: my_job <== job name job_subname: NULL job_style: NULL detached: NULL session_id: 3221732807 <== session id slave_process_id: NULL slave_os_process_id: NULL resource_consumer_group: NULL running_instance: NULL elapsed_time: 25824413 <== already running time in microsecond(us) cpu_used: NULL destination_owner: NULL destination: NULL credential_owner: NULL credential_name: NULL job_class: DEFAULT_JOB_CLASS 1 row in set (0.001 sec)如果上述作业已完成运行，则上述记录将从
oceanbase.__all_virtual_tenant_scheduler_running_job中删除：MySQL [oceanbase]> select * from oceanbase.__all_virtual_tenant_scheduler_running_job\G Empty set (0.001 sec)查看调度程序作业 my_job 已经完成计划的运行历史记录。
obclient [SYS]> select job_name, status, code, run_duration, message, gmt_create, gmt_modified from dba_scheduler_job_run_details where job_name='my_job'; +----------+-----------+-------+---------------+-------------------+------------------------------+------------------------------+ | JOB_NAME | STATUS | CODE | RUN_DURATION | MESSAGE | GMT_CREATE | GMT_MODIFIED | +----------+-----------+-------+---------------+-------------------+------------------------------+------------------------------+ | my_job | SCHEDULED | -4012 | +000 00:00:00 | check job timeout | 25-OCT-25 01.43.56.557288 PM | 25-OCT-25 01.43.56.557288 PM | | my_job | SCHEDULED | 0 | +000 00:01:00 | SUCCESS | 25-OCT-25 01.44.26.745780 PM | 25-OCT-25 01.44.26.745780 PM | | my_job | SCHEDULED | -4012 | +000 00:00:00 | check job timeout | 25-OCT-25 01.48.52.019751 PM | 25-OCT-25 01.48.52.019751 PM | | my_job | SCHEDULED | 0 | +000 00:01:00 | SUCCESS | 25-OCT-25 01.49.22.021935 PM | 25-OCT-25 01.49.22.021935 PM | | my_job | SCHEDULED | -4012 | +000 00:00:00 | check job timeout | 25-OCT-25 01.53.52.017487 PM | 25-OCT-25 01.53.52.017487 PM | | my_job | SCHEDULED | 0 | +000 00:01:00 | SUCCESS | 25-OCT-25 01.54.22.021620 PM | 25-OCT-25 01.54.22.021620 PM | +----------+-----------+-------+---------------+------------------- +------------------------------+------------------------------+ 6 rows in set (0.006 sec)注意
此调度任务 my_job 超时的原因是，创建此任务时未指定其
max_run_duration参数。如果未指定max_run_duration参数，则此任务的默认超时时间为 30 秒。删除调度程序作业 my_job。
obclient [SYS]> BEGIN DBMS_SCHEDULER.DROP_JOB(job_name => 'my_job'); COMMIT; END / Query OK, 1 row affected (0.035 sec) obclient [SYS]> select job_name,job_action,comments,start_date,end_date from dba_scheduler_jobs where job_name='my_job'; Empty set (0.000 sec)
适用版本
OceanBase 数据库 V4.2.1 BP5（oceanbase-4.2.1.5-105000072024041817）及更高版本。
