metlaink上曾经有一篇文章上大致列举了job不能运行的可能的十多种原因，包括sga变量kkjsre为0；uptime超过497天（solarisd 系统上的bug 3427424）；JOB_QUEUE_PROCESSES为0;_SYSTEM_TRIG_ENABLED 为false等等，还有些人为的原因.

这里仅仅想讨论一下如何简单的broke系统中所有用户的job，或者如何使用sys用户remove其他用户的job.

oracle有一个undocument的函数DBMS_IJOB，可以让sysdba改变其他用户job的状态.

(由于DBMS_IJOB是undocument的，因此不受oracle的支持，建议仅在测试库上进行测试，生产库要想使用这个功能，请在oracle support的支持下使用！！)

例如：

col INTERVAL for a20
col what for a20
col schema_user for a10
set linesize 132
select job, broken,schema_user, last_date, last_sec, next_date, next_sec,
broken, failures, interval, what from dba_jobs;

sys@TSMISC02> l
  1  select job, broken,schema_user, last_date, last_sec, next_date, next_sec,
  2* broken, failures, interval, what from dba_jobs
sys@TSMISC02> /

       JOB B SCHEMA_USE LAST_DATE LAST_SEC         NEXT_DATE NEXT_SEC         B   FAILURES INTERVAL             WHAT
---------- - ---------- --------- ---------------- --------- ---------------- - ---------- -------------------- --------------------
         9 N PERFSTAT   28-APR-06 10:08:32         28-APR-06 10:16:02         N          0 SYSDATE+(1/192)      statspack.snap;
        10 N LUNAR      28-APR-06 10:06:27         28-APR-06 10:13:57         N          0 SYSDATE+(1/192)      statspack.snap;
         8 Y LUNAR      24-FEB-06 02:04:26         01-JAN-00 00:00:00         Y         16 SYSDATE+(1/192)      del_profile_result;

Elapsed: 00:00:00.00
sys@TSMISC02>
sys@TSMISC02> Execute DBMS_IJOB.BROKEN(8, false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
sys@TSMISC02>
sys@TSMISC02> select job, broken,schema_user, last_date, last_sec, next_date, next_sec,
  2  broken, failures, interval, what from dba_jobs;

       JOB B SCHEMA_USE LAST_DATE LAST_SEC         NEXT_DATE NEXT_SEC         B   FAILURES INTERVAL             WHAT
---------- - ---------- --------- ---------------- --------- ---------------- - ---------- -------------------- --------------------
         9 N PERFSTAT   28-APR-06 10:08:32         28-APR-06 10:16:02         N          0 SYSDATE+(1/192)      statspack.snap;
        10 N LUNAR      28-APR-06 10:14:02         28-APR-06 10:21:32         N          0 SYSDATE+(1/192)      statspack.snap;
         8 N LUNAR      24-FEB-06 02:04:26         28-APR-06 10:14:24         N         16 SYSDATE+(1/192)      del_profile_result;

Elapsed: 00:00:00.00
sys@TSMISC02>

当然，你还可以将这个过程自己封装一下，就可以方便的实现改变数据库中所有job的状态了：

sys@TSMISC02> select job, broken,schema_user, last_date, last_sec, next_date, next_sec,
  2  broken, failures, interval, what from dba_jobs;

       JOB B SCHEMA_USE LAST_DATE LAST_SEC         NEXT_DATE NEXT_SEC         B   FAILURES INTERVAL             WHAT
---------- - ---------- --------- ---------------- --------- ---------------- - ---------- -------------------- --------------------
         9 N PERFSTAT   28-APR-06 10:08:32         28-APR-06 10:16:02         N          0 SYSDATE+(1/192)      statspack.snap;
        10 N LUNAR      28-APR-06 10:14:02         28-APR-06 10:21:32         N          0 SYSDATE+(1/192)      statspack.snap;
         8 N LUNAR      24-FEB-06 02:04:26         28-APR-06 10:14:24         N         16 SYSDATE+(1/192)      del_profile_result;

Elapsed: 00:00:00.00
sys@TSMISC02>
sys@TSMISC02> Execute break (TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
sys@TSMISC02> select job, broken,schema_user, last_date, last_sec, next_date, next_sec,
  2  broken, failures, interval, what from dba_jobs;

       JOB B SCHEMA_USE LAST_DATE LAST_SEC         NEXT_DATE NEXT_SEC         B   FAILURES INTERVAL             WHAT
---------- - ---------- --------- ---------------- --------- ---------------- - ---------- -------------------- --------------------
         9 Y PERFSTAT   28-APR-06 10:16:07         01-JAN-00 00:00:00         Y          0 SYSDATE+(1/192)      statspack.snap;
        10 Y LUNAR      28-APR-06 10:14:02         01-JAN-00 00:00:00         Y          0 SYSDATE+(1/192)      statspack.snap;
         8 Y LUNAR      24-FEB-06 02:04:26         01-JAN-00 00:00:00         Y         16 SYSDATE+(1/192)      del_profile_result;

Elapsed: 00:00:00.01
sys@TSMISC02>

还可以改变某一个用户的状态：

sys@TSMISC02> /

       JOB SCHEMA_USE LAST_DATE LAST_SEC         NEXT_DATE NEXT_SEC         B   FAILURES INTERVAL             WHAT
---------- ---------- --------- ---------------- --------- ---------------- - ---------- -------------------- --------------------
         9 PERFSTAT   28-APR-06 10:16:07         28-APR-06 10:32:43         N          0 SYSDATE+(1/192)      statspack.snap;
        10 LUNAR      28-APR-06 10:21:33         28-APR-06 10:32:43         N          0 SYSDATE+(1/192)      statspack.snap;
         8 LUNAR      28-APR-06 10:21:23         28-APR-06 10:32:43         N         17 SYSDATE+(1/192)      del_profile_result;

Elapsed: 00:00:00.00
sys@TSMISC02>

sys@TSMISC02> execute remove ('PERFSTAT');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
sys@TSMISC02> /

       JOB SCHEMA_USE LAST_DATE LAST_SEC         NEXT_DATE NEXT_SEC         B   FAILURES INTERVAL             WHAT
---------- ---------- --------- ---------------- --------- ---------------- - ---------- -------------------- --------------------
        10 LUNAR      28-APR-06 10:21:33         28-APR-06 10:32:43         N          0 SYSDATE+(1/192)      statspack.snap;
         8 LUNAR      28-APR-06 10:21:23         28-APR-06 10:32:43         N         17 SYSDATE+(1/192)      del_profile_result;

Elapsed: 00:00:00.00
sys@TSMISC02>

多方便呀，呵呵，关键是我们的系统有这样特殊的需求。

发表于 @ 2006年04月28日 11:57:00|评论(loading...)|编辑