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
No comments:
Post a Comment