create or replace view dba_jobs_running 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; comment on column DBA_JOBS_RUNNING.SID is 'Identifier of process which is executing the job. See v$lock.'; comment on column DBA_JOBS_RUNNING.JOB is 'Identifier of job. This job is currently executing.'; comment on column DBA_JOBS_RUNNING.FAILURES is 'How many times has this job started and failed since its last success?'; comment on column DBA_JOBS_RUNNING.LAST_DATE is 'Date that this job last successfully executed'; comment on column DBA_JOBS_RUNNING.LAST_SEC is 'Same as LAST_DATE. This is when the last successful execution started.'; comment on column DBA_JOBS_RUNNING.THIS_DATE is 'Date that this job started executing (usually null if not executing)'; comment on column DBA_JOBS_RUNNING.THIS_SEC is 'Same as THIS_DATE. This is when the last successful execution started.'; comment on column DBA_JOBS_RUNNING.INSTANCE is 'The instance number restricted to run the job';
In RAC we can use next script to find all running jobs across all nodes:
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, v.INST_ID instance from sys.job$ j, gv$lock v where v.type = 'JQ' and j.job(+) = v.id2;Also you can create some view according to this query and call it dba_jobs_running_rac or something like that and in RAC database use that view to find all running jobs across all nodes.
For example :
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'), 1, 8) LAST_SEC, THIS_DATE, substr(to_char(this_date, 'HH24:MI:SS'), 1, 8) 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