未分类

Oracle jobs

oracle

河北·翡翠岛 2007.5

Job简介

Job的作用

定时运行一个存储过程等，有点类似于unix的crontab。

创建一个job

这里，先假设有一个存储过程cdc_otest

1
2
3
4
5
6
create or replace procedure cdc_otest
AS
BEGIN
  insert into oyd_test(user_no) values('123');
  COMMIT;
end;

oyd_test表的结构

1
2
3
4
5
6
7
create table OYD_TEST
(
  USER_NO       VARCHAR2(10) not null,
  DEVICE_NUMBER VARCHAR2(10),
  FLAG          VARCHAR2(12),
  SYS_DATE      DATE default sysdate not null
)

下面会尽可能的提供sql plus和pl/sql developer两种工具的操作方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sql plus
SQL> variable job1 number;
 
SQL> begin
  2  dbms_job.submit(:job1,'cdc_otest;',sysdate,'sysdate+1/1440');
  3  end;
  4  /
 
PL/SQL procedure successfully completed
job1
---------
1166
 
SQL>

当然，也可以直接用exec(或execute)代替繁琐的begin、end和/

1
2
3
4
5
SQL> variable job1 number;
 
SQL> exec dbms_job.submit(:job1,'cdc_otest;',sysdate,'sysdate+1/1440');
 
Pl/sql dev

运行一个job

1
2
3
4
5
SQL> exec dbms_job.run(1166);
 
PL/SQL procedure successfully completed
 
SQL>

删除一个job

1
2
3
4
5
SQL> exec dbms_job.remove(1166)
 
PL/SQL procedure successfully completed
 
SQL>

Job参数

查看job表

1
2
3
select * from dba_jobs;
select * from all_jobs;
select * from user_jobs;

各字段含义

DBA_JOBS
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
字段（列） 类型 描述
JOB NUMBER 任务的唯一标示号
LOG_USER VARCHAR2(30) 提交任务的用户
PRIV_USER VARCHAR2(30) 赋予任务权限的用户
SCHEMA_USER VARCHAR2(30) 对任务作语法分析的用户模式
LAST_DATE DATE 最后一次成功运行任务的时间
LAST_SEC VARCHAR2(8) 如HH24:MM:SS格式的last_date日期的小时，分钟和秒
THIS_DATE DATE 正在运行任务的开始时间，如果没有运行任务则为null
THIS_SEC VARCHAR2(8) 如HH24:MM:SS格式的this_date日期的小时，分钟和秒
NEXT_DATE DATE 下一次定时运行任务的时间
NEXT_SEC VARCHAR2(8) 如HH24:MM:SS格式的next_date日期的小时，分钟和秒
TOTAL_TIME NUMBER 该任务运行所需要的总时间，单位为秒
BROKEN VARCHAR2(1) 标志参数，Y标示任务中断，以后不会运行
INTERVAL VARCHAR2(200) 用于计算下一运行时间的表达式
FAILURES NUMBER 任务运行连续没有成功的次数
WHAT VARCHAR2(2000) 执行任务的PL/SQL块
CURRENT_SESSION_LABEL RAW MLSLABEL 该任务的信任Oracle会话符
CLEARANCE_HI RAW MLSLABEL 该任务可信任的Oracle最大间隙
CLEARANCE_LO RAW MLSLABEL 该任务可信任的Oracle最小间隙
NLS_ENV VARCHAR2(2000) 任务运行的NLS会话设置
MISC_ENV RAW(32) 任务运行的其他一些会话参数

INTERVAL的设置

每天：trunc(sysdate+1)
每周：trunc(sysdate+7)
每月：trunc(sysdate+30)
每个星期日：next_day(trunc(sysdate),’SUNDAY’)
每天6点：trunc(sysdate+1)+6/24
半个小时：sysdate+30/1440

每个小时的第15分钟运行，比如：8:15，9:15，10:15…：trunc(sysdate,’hh’)+75/1440
每天午夜12点 ‘TRUNC(SYSDATE + 1)’
每天早上8点30分 ‘TRUNC(SYSDATE + 1) + （860+30）/(2460)’
每星期二中午12点 ‘NEXT_DAY(TRUNC(SYSDATE ), ‘’TUESDAY’’ ) + 12/24’
每个月第一天的午夜12点 ‘TRUNC(LAST_DAY(SYSDATE ) + 1)’
每个季度最后一天的晚上11点 ‘TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ‘Q’ ) -1/24’
每星期六和日早上6点10分 ‘TRUNC(LEAST(NEXT_DAY(SYSDATE, ‘’SATURDAY”), NEXT_DAY(SYSDATE, “SUNDAY”))) + （6×60+10）/（24×60）’

Trunc函数
有点类似于round函数，不同之处在于后者是四舍五入而前者是截取

select trunc(12.35,1) from dual;
select trunc(12.35,1) from dual;
select ROUND(12.35,1) from dual;
select trunc(sysdate,’dd’) from dual;
select trunc(sysdate,’hh’) from dual;
select trunc(sysdate) from dual;

Job不运行

决定job是否运行的参数job_queue_processes
如果job_queue_processes为0，则job不运行；如果job_queue_processes为n，则系统job大于n时会引起排队
查看job_queue_processes

1
2
3
4
5
SQL> show parameter job_queue_processes
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10

或show parameter job，自动通配。

修改job_queue_processes(重启job，SNP进程死了时使用)

1
2
3
4
5
6
7
SQL> alter system set job_queue_processes=0;--关闭job进程，等待5--10秒钟
 
System altered
 
SQL> alter system set job_queue_processes=10; --恢复原来的值
 
System altered

job_queue_processes最大值为1000

next_date为4000年1月1日

要不job在running
要不就是状态是break

job如果由于某种原因未能成功之行，oracle将重试16次后，还未能成功执行，将被标记为broken重新启动状态为broken的job
失败的数量会记录在failures

更改break状态有两种方法

  • 一是直接运行job
    SQL> exec dbms_job.run(1165)
  • 二是更改broken
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    SQL> exec dbms_job.broken(1165,false)
     
    PL/SQL procedure successfully completed
     
    SQL> commit;
     
    Commit complete
     
    SQL> exec dbms_job.broken(1165,true)
     
    PL/SQL procedure successfully completed
     
    SQL> commit;
     
    Commit complete

检查是否在running的表为select * from dba_jobs_running
但是在我的库里查询很慢，原因未明。

Job后台

select * from v$bgprocess where name like ‘CJQ%’;

Job的包

select * from all_objects where object_name = upper(‘dbms_job’);

分享到