Jcat
宠辱不惊,闲看庭前花开花落~~
posts - 173,comments - 67,trackbacks - 0
--最多同时运行的JOB个数;如果太小,JOB就排队等待;如果为0,就没有JOB会被执行。
SQL> show parameter job_queue_processes
NAME                                        TYPE          VALUE
--------------------------         ----------    -------------
job_queue_processes         integer          10


--一个什么也不做的SP

CREATE OR REPLACE PROCEDURE mytest
IS
BEGIN
    NULL;
END;

--一个往表里写数据的SP
CREATE OR REPLACE PROCEDURE mytest
IS
BEGIN
    insert into test_table values(...);
END;

--定时调用SP,10秒(86400秒=1天)
SQL> VARIABLE job_id NUMBER;
SQL> BEGIN
           -- :job_id中的冒号表示冒号后面的是变量,类似SQL Server的@
           DBMS_JOB.SUBMIT(:job_id, 'mytest;', sysdate, 'sysdate + 10 / 86400') ;
           COMMIT;  --记得一定要commit哦
           END;

--查看刚才生成的Job ID
SQL> set serveroutput on
SQL> execute dbms_output.put_line(:job_id)  --这里倒是不需要commit,直接execute就好了
318

--查看所有Job
 select * from user_jobs;

--删除Job
SQL> BEGIN
           DBMS_JOB.REMOVE(123); --123是Job ID
           COMMIT;
           END;


如果Job由于某种原因未能成功运行,Oracle将重试16次,之后如果还未能成功运行,将被标记为Broken。



http://www.blogjava.net/Jcat/archive/2009/12/17/306315.html
从10g开始,DBMS_SCHEDULER 逐步会替换掉 DBMS_JOB

DBMS_JOB has been around forever, and now it is deprecated. Although DBMS_JOB still exists in 10g and 11g, but only for backward compatibility. No new features are being added to dbms_job and you will likely quickly run into its limitations. Oracle recommends the use of DBMS_SCHEDULER in releases 10g and up. DBMS_SCHEDULER is a much more robust package and fully-featured than DBMS_JOB. To use the DBMS_SCHEDULER package a user must be granted the CREATE JOB privilege.

After replace DBMS_JOB with DBMS_SCHEDULER for all jobs successful, the job_queue_processes parameter can now be set to zero.
SQL> alter system set job_queue_processes=0;

posted on 2009-04-16 15:42 Jcat 阅读(751) 评论(0)  编辑  收藏 所属分类: Database

只有注册用户登录后才能发表评论。


网站导航: