1. 创建job.
PROCEDURE SUBMIT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN   
 LUSER                          VARCHAR2                IN   
 PUSER                          VARCHAR2                IN   
 CUSER                          VARCHAR2                IN   
 NEXT_DATE                      DATE                    IN   
 INTERVAL                       VARCHAR2                IN   
 BROKEN                         BOOLEAN                 IN   
 WHAT                           VARCHAR2                IN   
 NLSENV                         VARCHAR2                IN   
 ENV                            RAW                     IN   

SQL> begin
  2    dbms_ijob.submit(1,'SYS','SCOTT','TONGJW',sysdate + 1/24,
  3       'sysdate+1/24',false,'null;',
  4       'NLS_LANGUAGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' '||
  5       'NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA'' '||
  6       'NLS_NUMERIC_CHARACTERS=''.,'' '||
  7       'NLS_DATE_FORMAT=''MM/DD/YYYY HH24:MI:SS'' '||
  8       'NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' ',
  9      '0102000200000000');
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> col log_user format a10
SQL> col priv_user format a10
SQL> col schema_user format a10
SQL> col what format a15
SQL> commit;

Commit complete.

SQL> set linesize 100
SQL> select job,log_user,priv_user,schema_user,what,next_date
  2  from dba_jobs
  3  /

       JOB LOG_USER   PRIV_USER  SCHEMA_USE WHAT            NEXT_DATE                              
---------- ---------- ---------- ---------- --------------- ---------                              
         1 SYS        SCOTT      TONGJW     null;           31-MAR-05                       
2. 运行其他用户的job.
PROCEDURE RUN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN     DEFAULT
 FORCE                          BOOLEAN                 IN     DEFAULT

SQL> exec dbms_ijob.run(1);

PL/SQL procedure successfully completed.

3. 修改job的运行时间间隔.
PROCEDURE INTERVAL
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN   
 INTERVAL                       VARCHAR2                IN   
SQL> exec dbms_ijob.interval(1,'sysdate+2/24');

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select job,log_user,priv_user,schema_user,what,next_date
  2  from dba_jobs
  3  /

       JOB LOG_USER   PRIV_USER  SCHEMA_USE WHAT            NEXT_DATE                              
---------- ---------- ---------- ---------- --------------- -------------------                    
         1 SYS        SCOTT      TONGJW     null;           2005-03-31 01:52:18             
4. 修改job的下一次运行时间:-)
PROCEDURE NEXT_DATE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN   
 NEXT_DATE                      DATE                    IN   
SQL> exec dbms_ijob.next_date(1,sysdate+2/24);

PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,schema_user,what,next_date
  2  from dba_jobs
  3  /

       JOB LOG_USER   PRIV_USER  SCHEMA_USE WHAT            NEXT_DATE
---------- ---------- ---------- ---------- --------------- -------------------
         1 SYS        SCOTT      TONGJW     null;           2005-03-31 02:57:45

5. 修改job的执行内容
PROCEDURE WHAT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN   
 WHAT                           VARCHAR2                IN   
SQL> exec dbms_ijob.what(1,'null;null;');

PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,schema_user,what,next_date
  2  from dba_jobs
  3  /

       JOB LOG_USER   PRIV_USER  SCHEMA_USE WHAT            NEXT_DATE
---------- ---------- ---------- ---------- --------------- -------------------
         1 SYS        SCOTT      TONGJW     null;null;      2005-03-31 02:57:45

SQL>

6.  中断broken jobs.

PROCEDURE BROKEN
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN   
 BROKEN                         BOOLEAN                 IN   
 NEXT_DATE                      DATE                    IN     DEFAULT

SQL> exec dbms_ijob.broken(1,true);

PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,schema_user,what,next_date,broken
  2  from dba_jobs
  3  /

       JOB LOG_USER   PRIV_USER  SCHEMA_USE WHAT            NEXT_DATE
B
---------- ---------- ---------- ---------- --------------- -------------------
-
         1 SYS        SCOTT      TONGJW     null;null;      4000-01-01 00:00:00
Y

SQL> select job,log_user,priv_user,schema_user,what,next_date,broken
  2  from user_jobs
  3  /

no rows selected

SQL>

7. 删除job.
PROCEDURE REMOVE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 JOB                            BINARY_INTEGER          IN   

SQL> exec dbms_ijob.remove(1);

PL/SQL procedure successfully completed.

SQL> select job,log_user,priv_user,schema_user,what,next_date,broken
  2  from dba_jobs
  3  /

no rows selected

SQL>

删除job 忘记commit 啦

2. 运行其他用户的job.
SQL> exec dbms_ijob.run(1);
PL/SQL procedure successfully completed.

只能使用job的schema user登陆执行dbms_job.run oracle的job管理搞得复杂西西的，应该有一个manage all job的系统权限给dba.

太完了, 将效果做出来以后, 就不管了..

顺便也将你上次在itput上的那个问题给做了一下, 三个user都不同, 估计又有人会晕掉咯:-)

呵呵，我刚刚把玉面飞龙的帖子回完，就看见你这篇文章了。
不过我估计它的本意一定是tom实现的那种，而不是用ijob实现。

tom那种才是实际应用的例子,

我这个之只是为了演示的方便:-)

sorry,没有仔细看。原来是ijob。

oracle总是藏些东西 不告诉大家；有宝不漏。

标题

在此添加评论
表情符号:

称呼

邮箱地址（可选）

个人主页（可选）

记住我？