如何使用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的狀態.

(由於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>

 

 

多方便呀，呵呵，關鍵是我們的系統有這樣特殊的需求。