客户环境12.2.0.1,三节点RAC需要，将一个正在运行的Job session kill掉，但是通过DBA_JOBS_RUNNING发现，无法发现其它实例运行的JOB，因此需要登陆多台实例进行查询验证。 将SQL改写能否可行，以及查询基表是否有什么风险。
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, vlock v where v.type = 'JQ' and j.job (+)= v.id2; 可以发现是通过vlock type=JQ说明是正在运行的JOB session信息。
改写，v$lock=>gv$lock,添加查询字段inst_id.
目的，能查询正在运行的job再哪个实例，而不用rac环境每套登陆查询。
select v.inst_id,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, gvlock v where v.type = 'JQ' and j.job (+)= v.id2; 再次改写，添加gvsession, serial#字段
select v.inst_id,v.SID,s.serial#,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, 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;