ALL_JOBS

📄字数 1.9K

👁️阅读量 加载中...

功能描述

ALL_JOBS系统视图用于保存和管理定时作业相关信息。

字段说明

序号	字段名	类型	说明	V11	V12
0	DB_ID	INTEGER	库ID	√	√
1	USER_ID	INTEGER	创建者（将以该用户身份启动任务）	√	√
2	JOB_ID	INTEGER	定时作业ID	√	√
3	JOB_NAME	VARCHAR	Job名	√	√
4	JOB_GRP_ID	INTEGER	Job组ID	√	√
5	JOB_NO	INTEGER	Job序号（在一个job组内的序号）	√	√
6	JOB_TYPE	VARCHAR	Job类型(0:plsql_block; 1:stored_procedure; 2:plsql_command)	√	√
7	JOB_PARAM_NUM	INTEGER	Job参数个数	√	√
8	JOB_PARAM	BINARY	Job参数	√	√
9	JOB_ACTION	CLOB	动作语句（作业被执行时将执行该语句）	√	√
10	BEGIN_T	DATETIME	开始时间	√	√
11	END_T	DATETIME	结束时间	√	√
12	REPET_INTERVAL	VARCHAR	频度描述字串	√	√
13	TRIG_EVENTS	VARCHAR	触发事件名	√	√
14	LAST_RUN_T	DATETIME	最后一次运行时间	√	√
15	STATE	CHAR	状态	√	√
16	ENABLE	BOOLEAN	该作业是否可用	√	√
17	AUTO_DROP	BOOLEAN	作业到达结束时间后是否自动删除	√	√
18	IS_SYS	BOOLEAN	是否系统内建	√	√
19	COMMENTS	VARCHAR	注释信息	√	√
20	RESERVED1	VARCHAR	保留字段	√	√
21	RESERVED2	VARCHAR	保留字段	√	√

(STATE)定时作业状态属性
访问SYS_JOBS的STATE字段可以获取对应定时作业的工作状态，STATE可以是以下两个值之一:

序号	STATE	描述
1	IDLE	标识定时作业为空闲态，等待执行
2	RUNNING	标识定时作业正在运行

应用举例

• 创建plsql_block定时作业，查询job相关信息

SQL> EXECUTE DBMS_SCHEDULER.CREATE_JOB('job_plsql_info',
     'plsql_block',
     'DECLARE BEGIN SEND_MSG(1);END;',
     0,
     sysdate,
     'FREQ=YEARLY;INTERVAL=1;',
     sysdate+2/24/60,
     'JOB CLASS',
     NULL,
     FALSE,
     NULL);

SQL> SELECT * FROM ALL_JOBS WHERE job_name='job_plsql_info';
+-------+---------+---------+----------------+------------+--------+-------------+---------------+-----------+------------+--------------------------+--------------------------+-------------------------+-------------+--------------------------+-------+--------+-----------+--------+----------+-----------+-----------+
| DB_ID | USER_ID | JOB_ID  |    JOB_NAME    | JOB_GRP_ID | JOB_NO |  JOB_TYPE   | JOB_PARAM_NUM | JOB_PARAM | JOB_ACTION |         BEGIN_T          |          END_T           |     REPET_INTERVAL      | TRIG_EVENTS |        LAST_RUN_T        | STATE | ENABLE | AUTO_DROP | IS_SYS | COMMENTS | RESERVED1 | RESERVED2 |
+-------+---------+---------+----------------+------------+--------+-------------+---------------+-----------+------------+--------------------------+--------------------------+-------------------------+-------------+--------------------------+-------+--------+-----------+--------+----------+-----------+-----------+
| 1     | 1       | 1048577 | job_plsql_info | <NULL>     | <NULL> | plsql_block | 0             | <NULL>    | <CLOB>     | 2025-06-25 10:09:07.704  | 2025-06-25 10:11:07.704  | FREQ=YEARLY;INTERVAL=1; | <NULL>      | 1970-01-01 00:00:00.000  | IDLE  | F      | F         | F      |          | <NULL>    | <NULL>    |
+-------+---------+---------+----------------+------------+--------+-------------+---------------+-----------+------------+--------------------------+--------------------------+-------------------------+-------------+--------------------------+-------+--------+-----------+--------+----------+-----------+-----------+

• 创建stored_procedure定时作业，查询job相关信息

SQL> CREATE TABLE tab_job_proc(id1 INT,name1 VARCHAR,dt DATETIME);

SQL> CREATE OR REPLACE PROCEDURE job_pro() IS
     BEGIN
     FOR i IN 1..10 LOOP
     INSERT INTO tab_job_proc VALUES(i,'job'||i,ADD_MONTHS(sysdate,i));
     END LOOP;
     END;

SQL>  EXECUTE DBMS_SCHEDULER.CREATE_JOB('job_pro_info',
      'stored_procedure',
      'JOB_PRO',
      0,
      sysdate,
      'FREQ=MONTHLY;INTERVAL=99;',
      sysdate+2/24/60,
      'JOB CLASS',
      TRUE,
      TRUE,
      'insert data');

SQL> SELECT * FROM ALL_JOBS WHERE job_name='job_pro_info';
+-------+---------+---------+--------------+------------+--------+------------------+---------------+-----------+------------+--------------------------+--------------------------+---------------------------+-------------+--------------------------+-------+--------+-----------+--------+-------------+-----------+-----------+
| DB_ID | USER_ID | JOB_ID  |   JOB_NAME   | JOB_GRP_ID | JOB_NO |     JOB_TYPE     | JOB_PARAM_NUM | JOB_PARAM | JOB_ACTION |         BEGIN_T          |          END_T           |      REPET_INTERVAL       | TRIG_EVENTS |        LAST_RUN_T        | STATE | ENABLE | AUTO_DROP | IS_SYS |  COMMENTS   | RESERVED1 | RESERVED2 |
+-------+---------+---------+--------------+------------+--------+------------------+---------------+-----------+------------+--------------------------+--------------------------+---------------------------+-------------+--------------------------+-------+--------+-----------+--------+-------------+-----------+-----------+
| 1     | 1       | 1048580 | job_pro_info | <NULL>     | <NULL> | stored_procedure | 0             | <NULL>    | <CLOB>     | 2025-06-25 10:13:57.938  | 2025-06-25 10:15:57.938  | FREQ=MONTHLY;INTERVAL=99; | <NULL>      | 1970-01-01 00:00:00.000  | IDLE  | T      | T         | F      | insert data | <NULL>    | <NULL>    |
+-------+---------+---------+--------------+------------+--------+------------------+---------------+-----------+------------+--------------------------+--------------------------+---------------------------+-------------+--------------------------+-------+--------+-----------+--------+-------------+-----------+-----------+

相关系统表

• SYS_JOBS