oracle jobs 学习

一、准备学习
开始dbms_job学习前，先认识一个参数job_queue_processes

a、job_queue_processes参数决定了job作业能够使用的总进程数。
b、当该参数为0值，任何job都不会被执行，建议合理设置该值且至少大于1。
c、对于job运行时间也应该尽量合理的设置间隔以及启动时间。
d、如果同一时间内运行的Job数很多，过小的参数值导致job不得不进行等待。而过大的参数值则消耗更多的系统资源。
f、对于存在依赖关系的job，尽可能将其进行合并到一个job中，如使用chain等。

在使用dbms_jobs时首先确认job_queue_processes的值（10g系统默认为10）

SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10

二、创建一个job

SQL> create table a(a date);

Table created.

SQL> create or replace procedure job_test as
 begin
 insert into a values(sysdate);
 end;
 /

Procedure created.

SQL> exec job_test;

PL/SQL procedure successfully completed.

SQL> select * from a;

A
-------------------
2013-05-31 15:45:32

创建一个job任务，时间间隔为每秒一次

variable job1 number;
begin
sys.dbms_job.submit(job => :job1,
what => 'job_test;',
next_date => sysdate,
interval => 'sysdate+1/64800');--每天64800秒，即每秒执行一次，但是实际oracle的jobs不能精确到秒，这里只是为了快速测试效果
commit;
end;

SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs;

       JOB N_DATE              WHAT       INTERVAL             B
---------- ------------------- ---------- -------------------- -
2013-05-31 15:49:24 job_test;  sysdate+1/64800      N

SQL> select count(*) from a;

  COUNT(*)
----------

SQL> /

  COUNT(*)
----------

SQL> /

  COUNT(*)
----------

SQL> select * from a;

A
-------------------
2013-05-31 15:45:32
2013-05-31 15:48:58
2013-05-31 15:49:03
2013-05-31 15:49:08
2013-05-31 15:49:13
2013-05-31 15:49:18
2013-05-31 15:49:23
2013-05-31 15:49:28
2013-05-31 15:49:33
2013-05-31 15:49:38
2013-05-31 15:49:43
rows selected.

三、修改job执行时间

SQL> exec dbms_job.change(4,null,null,'sysdate+1/1440');

PL/SQL procedure successfully completed.

SQL> commit;  --一定要记得commit否则重启后会丢失

Commit complete.

SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs;

       JOB N_DATE              WHAT       INTERVAL             B
---------- ------------------- ---------- -------------------- -
2013-05-31 15:57:08 job_test;  sysdate+1/1440       N



SQL> truncate table a;

Table truncated.

SQL> select * from a;

no rows selected

SQL> select * from a;

A
-------------------
2013-05-31 15:57:08

SQL> /

A
-------------------
2013-05-31 15:57:08
2013-05-31 15:58:08
2013-05-31 15:59:08
2013-05-31 16:00:08
2013-05-31 16:01:08

四、停止job

SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs;

       JOB N_DATE              WHAT       INTERVAL             B
---------- ------------------- ---------- -------------------- -
2013-05-31 16:12:00 job_test;  trunc(sysdate,'mi')+ N
                                          1/1440


SQL> exec dbms_job.broken(4,true);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>  select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs;

       JOB N_DATE              WHAT       INTERVAL             B
---------- ------------------- ---------- -------------------- -
4000-01-01 00:00:00 job_test;  trunc(sysdate,'mi')+ Y
                                          1/1440

五、重新开始job

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

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>  select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs;

       JOB N_DATE              WHAT       INTERVAL             B
---------- ------------------- ---------- -------------------- -
2013-05-31 16:36:24 job_test;  trunc(sysdate,'mi')+ N
                                          1/1440

六、删除job

SQL> exec dbms_job.remove(4);

PL/SQL procedure successfully completed.

SQL>  select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs;

no rows selected

SQL> rollback;--不commit咱rollback看看

Rollback complete.

SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs;

       JOB N_DATE              WHAT       INTERVAL             B
---------- ------------------- ---------- -------------------- -
2013-05-31 16:36:24 job_test;  trunc(sysdate,'mi')+ N
                                          1/1440
--jobs又回来了

SQL>  exec dbms_job.remove(4);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select job,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as n_date,what,interval,broken from user_jobs;

no rows selected
--commit后就真的没了