登陆每个数据库实例，执行如下SQL，根据JOB信息,业务人员确认JOB信息，得到SID

select * from dba_jobs_running;

*如果开发人员无法确认JOB_ID,可以通过

select * from DBA_JOBS; WHAT--字段追踪job执行的PLSQL

然后根据v$session

select sid,serial# from v$session where sid=xx;

select a.spid from v$process a ,v$session b where a.addr=b.paddr and b.sid in (select sid from dba_jobs_running);

alter system kill session 'sid,serial#' immediate;

问题已处理

或者通过

--禁用JOB
SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);
--启用JOB
SQL> EXEC DBMS_JOB.BROKEN(job#,FALSE);

broken  --官方文档定义,TURE则损坏状态
Sets the job as broken or not broken. TRUE sets it as broken; FALSE sets it as not broken.

SQL> select owner,object_name,object_id,status,object_type from dba_objects where object_name='DBA_JOBS_RUNNING';

OWNER      OBJECT_NAME                     OBJECT_ID STATUS  OBJECT_TYPE

---------- ------------------------------ ---------- ------- -------------------

SYS        DBA_JOBS_RUNNING                     4923 VALID   VIEW

PUBLIC     DBA_JOBS_RUNNING                     4924 VALID   SYNONYM

SQL> select dbms_metadata.get_ddl('VIEW','DBA_JOBS_RUNNING','SYS') ddl_text from dual;

DDL_TEXT

--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_JOBS_RUNNING" ("SID", "JOB", "FAILURES

", "LAST_DATE", "LAST_SEC", "THIS_DATE", "THIS_SEC", "INSTANCE") AS

  select v.SID, v.id2 JOB, j.FAILURES,

    LAST_DATE, substr(to_char(last_date,'HH24:MI:SS'),1,8) LAST_SEC,

    THIS_DATE, substr(to_char(this_date,'HH24:MI:SS'),1,8) THIS_SEC,

    j.field1 INSTANCE

  from sys.job$ j, v$lock v

  where v.type = 'JQ' and j.job (+)= v.id2

从上述SQL中,我们能知道,为什么DBA_JOBS_RUNNING 这个视图无法获取RAC其它实例正在运行的JOB,因为获取的条件是V$LOCK TYPE=JQ,其它实例信息无法捕捉。

想法,使用一个SQL,能够获取到RAC环境下所有运行的JOB

添加了JOB运行的实例ID，RAC环境无需登陆多个节点查询
  select v.inst_id,v.SID, v.id2 JOB, j.FAILURES,

    LAST_DATE, substr(to_char(last_date,'HH24:MI:SS'),,) LAST_SEC,

    THIS_DATE, substr(to_char(this_date,'HH24:MI:SS'),,) THIS_SEC,

    j.field1 INSTANCE

  from sys.job$ j, gv$lock v

  where v.type = 'JQ' and j.job (+)= v.id2;

  
添加了SESSION,SERIAL#信息，kill session更加快速

   select v.inst_id,v.SID,s.serial#,v.id2 JOB, j.FAILURES,

    LAST_DATE, substr(to_char(last_date,'HH24:MI:SS'),,) LAST_SEC,

    THIS_DATE, substr(to_char(this_date,'HH24:MI:SS'),,) THIS_SEC,

    j.field1 INSTANCE

  from sys.job$ j, gv$lock v,gv$session s

  where v.type = 'JQ' and j.job (+)= v.id2 and v.inst_id=s.inst_id and v.sid=s.sid;

--建议测试后谨慎操作

select 'alter system kill session '''||v.SID||','||s.serial#||',@'||v.inst_id||''' immediate; '
from sys.job$ j, gv$lock v,gv$session s
where v.type = 'JQ' and j.job (+)= v.id2 and v.inst_id=s.inst_id and v.sid=s.sid and v.id2=&job_id;

添加了JOB运行的实例ID，RAC环境无需登陆多个节点查询

select v.SID, v.id2 JOB, j.FAILURES, LAST_DATE, 
substr(to_char(last_date, 'HH24:MI:SS'), , ) LAST_SEC,
 THIS_DATE, substr(to_char(this_date, 'HH24:MI:SS'), , ) THIS_SEC, 
v.INST_ID instance 
from sys.job$ j, gv$lock v 
where v.type = 'JQ' and j.job(+) = v.id2; 

create or replace view dba_jobs_running_rac
 as 
select v.SID, v.id2 JOB, j.FAILURES,
 LAST_DATE, substr(to_char(last_date, 'HH24:MI:SS'), , ) LAST_SEC,
 THIS_DATE, substr(to_char(this_date, 'HH24:MI:SS'), , ) THIS_SEC,
 v.INST_ID instance
     from sys.job$ j, gv$lock v 
          where v.type = 'JQ' and j.job(+) = v.id2;

 select * from dba_jobs_running_rac；