如何使用sys用户remove其他用户的job
如何使用sys用户remove其他用户的job 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的状态，例如：
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>
多方便呀，呵呵，关键是我们的系统有这样特殊的需求。
