首页 > 代码库 > JOB

JOB

2024-07-15 20:18:03   231人阅读

 

1.1       描述

定时执行数据库中的PL/SQL块，如存储过程。可以减化前端编程。

 

1.2       简单示例

本示例运行环境：oracle10g + pl/sql +xp。

使用存储过程每一分钟为一张表添加一条记录。

  • 创建序列

create sequence student_id

minvalue 10

maxvalue 999999

start with 10

increment by 1

cache 2

cycle

order;

 

  •  创建存储过程

create or replace procedure p_test_job as

begin

  insert into student(sid) values (student_id.nextval);

  commit;

end;

 

 

  • 创建JOB

命令行创建

variable test_job number;              

begin

  sys.dbms_job.submit(job => :test_job,       --对应的唯一id（jobId<->jobName）唯一映射

                      what => ‘p_test_job;‘,   --调用的存储过程

                      next_date => sysdate,       --第一次执行时间

                      interval => ‘sysdate+1/1440‘);--执行间隔

  commit;

end;

/

 

说明：该语句需要在命令窗口中执行，begin至/的部分一行一行写入命令行时，会报错。复制后一次粘贴到命令行时就没有问题。

 

PL/SQL创建

declare

  test_job number;              

begin

  sys.dbms_job.submit(test_job, ‘p_test_job;‘,  sysdate, interval => ‘sysdate+1/1440‘);

  commit;

end;

 

test_job的值是submit过程返回回来的。Submit（）的括号中不能写注释。

 

  • 执行JOB

begin

   dbms_job.run(:test_job);        --执行job

end;

/

  

立即执行JOB,而不是按计划执行。

 

  • 查看创建的JOB

select * from sys.user_jobs;

输出结果：

 

 

  • 停止JOB

begin

  sys.dbms_job.broken(21,true);

end;

/

 参数true为停止，false为启动。

 

 

  •  启动JOB

begin

  sys.dbms_job.broken(21,false);

end;

 

 

  • 删除JOB

 

begin

  dbms_job.remove(21);     --删除job

end;

/

 

1.3     常用参数

 

序号

字段

类型

说明

1

JOB

NUMBER

任务的唯一标示号

2

LOG_USER

VARCHAR2(30)

提交任务的用户

3

PRIV_USER

VARCHAR2(30)

赋予任务权限的用户

4

SCHEMA_USER

VARCHAR2(30)

对任务作语法分析的用户模式

5

LAST_DATE

DATE

最后一次成功运行任务的时间

6

LAST_SEC

VARCHAR2(8)

如HH24:MM:SS格式的last_date日期的小时，分钟和秒

7

THIS_DATE

DATE

正在运行任务的开始时间，如果没有运行任务则为null

8

THIS_SEC

VARCHAR2(8)

如HH24:MM:SS格式的this_date日期的小时，分钟和秒

9

NEXT_DATE

DATE

下一次定时运行任务的时间

10

NEXT_SEC

VARCHAR2(8)

如HH24:MM:SS格式的next_date日期的小时，分钟和秒

11

TOTAL_TIME

NUMBER

该任务运行所需要的总时间，单位为秒

12

BROKEN

VARCHAR2(1)

标志参数，Y表示任务中断，以后不会运行；N表示任务正常。

13

INTERVAL

VARCHAR2(200)

用于计算下一运行时间的表达式

14

FAILURES

NUMBER

任务运行连续没有成功的次数

15

WHAT

VARCHAR2(2000)

执行任务的PL/SQL块

16

CLEARANCE_HI

RAW

该任务可信任的Oracle最大间隙

17

CLEARANCE_LO

RAW

MLSLABEL

18

NLS_ENV

VARCHAR2(2000)

任务运行的NLS会话设置

19

MISC_ENV

RAW(32)

任务运行的其他一些会话参数

20

CURRENT_SESSION_LABEL

RAW  MLSLABEL

该任务的信任Oracle会话符

 

1.4       几个调用方法

 

1.4.1        Submit创建job

定义

PROCEDURE Submit ( job       OUT binary_ineger,

What      IN  varchar2,

next_date IN  date,

interval  IN  varchar2,

no_parse  IN  booean:=FALSE)

 

参数说明

job参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作。

what参数 是将被执行的PL/SQL代码块; 或者为计划任务名，如果是多个计划任务，就都用分号隔开 。

next_date参数 指识何时将运行这个工作。

interval参数 何时这个工作将被重执行。

no_parse参数指示此工作在提交时或执行时是否应进行语法分析——TRUE

指示此PL/SQL代码在它第一次执行时应进行语法分析，

而FALSE指示本PL/SQL代码应立即进行语法分析。

 

如

 

declare

v_job number :=1;

begin

dbms_job.submit(v_job,‘dbms_output.put_line(1);‘,sysdate,‘sysdate+1/1440‘);

commit;

end;

 

  

 

1.4.2        其他方法

 

 

        dbms_job.run(v_job);         --立即运行job 

 

        dbms_job.broken(v_job,true,next_date);        --停止一个job,，next_date（某一时刻停止）也可是sysdate（立刻停止）。 

 

        dbms_job.remove(v_job);        --删除某个job 

 

        dbms_job.what(v_job,‘sp_fact_charge_code;‘);        --修改某个job名 

 

        dbms_job.next_date(v_job,sysdate);        --修改下一次运行时间 

 


 

停止其他用户的JOB

begin

 

    sys.dbms_ijob.broken(22,true);

    commit;

end;

 


 

以执行命令的方式启动

SQL> exec dbms_job.broken(22,false);

 

PL/SQL procedure successfully completed

 

 


 

需要提交。

 

1.5       查询JOB

 

dba_jobs

all_jobs

user_jobs

dba_jobs_running 包含正在运行job相关信息。 通常不查询该表，该表查询效率较低。

如：

select * from dba_jobs

 

1.6       频率设置

Trunc(Sysdate+1)是明天的日期，但Job频率的设置是指下一次的执行时间。

序号

时间

设置

1

每天8点10分

Trunc(Sysdate+1) + (8*60+10)/24*60

2

每个小时的第15分钟（如8:15，9:15，10:15）

trunc(sysdate,‘hh‘)+75/1440

3

半个小时

sysdate+30/1440

4

每天6点

trunc(sysdate+1)+6/24

5

每天

trunc(sysdate+1)

6

每周

trunc(sysdate+7)

7

每个星期日

next_day(trunc(sysdate),‘SUNDAY‘)

8

每月

trunc(sysdate+30)

9

每5秒执行一次

sysdate+1/86400

10

每15秒执行一次

sysdate+1/8640

11

每星期二中午12点

NEXT_DAY(TRUNC(SYSDATE ), ‘‘TUESDAY‘‘ ) + 12/24

12

每个月第一天的午夜12点

TRUNC(LAST_DAY(SYSDATE ) + 1)

13

每个季度最后一天的晚上11点

TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ‘Q‘ ) -1/24

14

每星期六和日早上6点10分

TRUNC(LEAST(NEXT_DAY(SYSDATE, ‘‘SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）

其他

1:每分钟执行

Interval => TRUNC(sysdate,‘mi‘) + 1/ (24*60)

 

2:每天定时执行

例如：每天的凌晨1点执行

Interval => TRUNC(sysdate) + 1 +1/ (24)

 

3:每周定时执行

例如：每周一凌晨1点执行

Interval => TRUNC(next_day(sysdate,‘星期一‘))+1/24

 

4:每月定时执行

例如：每月1日凌晨1点执行

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

 

5:每季度定时执行

例如每季度的第一天凌晨1点执行

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),‘Q‘) + 1/24

 

6:每半年定时执行

例如：每年7月1日和1月1日凌晨1点

Interval => ADD_MONTHS(trunc(sysdate,‘yyyy‘),6)+1/24

 

7:每年定时执行

例如：每年1月1日凌晨1点执行

Interval =>ADD_MONTHS(trunc(sysdate,‘yyyy‘),12)+1/24

 

 

 

1.7       参数设置

1.7.1        job_queue_process

oracle能够并发的job的数量。

l  查看job_queue_process

SQL> show parameter job_queue_process;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes                  integer     10

 

job_queue_process默认值为10，即同一时刻最多10个job运行。当job数目超过该参数时，就会进入等待队列。查看该参数需要有dba权限。

 

l  修改job_queue_process

SQL> alter system set job_queue_processes=39 scope=spfile;

 

System altered

 

最大值不能超过1000；当job_queue_process值为0时表示全部停止oracle的job。

这样修改oracle中job_queue_process的值是动态的，数据库重启后，将从admin\db\pfile的init文件中读取。

 

1.8       JOB不运行原因排查

1.8.1        job_queue_processes

当数据库JOB的数目超过job_queue_processes的值时，JOB就会排除等待。使用具有DBA权限的用户查看数据库正在执行的进程。

SQL> select * from v$bgprocess;

 

PADDR      PSERIAL# NAME  DESCRIPTION                                        ERROR

-------- ---------- ----- ----------------------------------------------- ----------

31A478FC          1 PMON  process cleanup                                      0

00                0 DIAG  diagnosibility process                               0

 

有的JOB可以瞬间执行完成，有的则需要一段时间。

另外一个是job_queue_interval，范围在1--3600之间，单位是秒，这个是唤醒JOB的process，因为每次snp运行完他就休息了，需要定期唤醒他，这个值不能太小，太小会影响数据库的性能。

 

job_queue_interval参数可以不设置。

 

1.8.2        查看dba_jobs表

select * from user_jobs

 

输出：

 

 

broken

Y : 表示该任务为停止状态

N : 表示该任务为启动状态

 last_date

最近一次job运行成功的结束时间。

l  next_date

根据设置的频率计算的下次执行时间。该时间并不一定是任务实际执行的时间，比如当任务实际执行的时间超过了任务之间的时间时。

 

1.8.3        查看任务是否仍然在执行

select * from dba_jobs_running

 

需要DBA权限。

如果一直在跑，就需要查找原因，比如是否锁表等。查看v$access和v$locked_object可查看锁表对象。

 

1.8.4        重启JOB

alter system set job_queue_processes=0 --关闭job进程，等待5--10秒钟

 

alter system set job_quene_processes=5 --恢复原来的值

 

找不到原因时，可以重启一下试试。这样的重启是面向所有的JOB的。

 

1.8.5        手工执行JOB

 

declare

 

begin

 

       dbms_job.run(<job>_ID);

 

end;

 


 

这样可以排查是否程序本身有错。

 

1.8.6        Oracle9i计数器

Oracle9i里面有一个BUG，当计数器到497天时，刚好达到它的最大值，再计数就会变成-1，继续计数就变成0了，然后计数器将不再跑了。如果碰到这种情况就得重启数据库。

 


联系
我们