Google it ....

Showing posts with label dba_jobs_running. Show all posts
Showing posts with label dba_jobs_running. Show all posts

Thursday, October 26, 2017

DBA_JOBS_RUNNING view is not showing all running DBMS_JOBS across the nodes in RAC

If you have Real Application Clusters Database (RAC) and you want to see currently running DBMS_JOBS across all nodes DBA_JOBS_RUNNING view in RAC is not showing all running jobs in Oracle 11g, It shows running jobs on instance where you querying DBA_JOBS_RUNNING, because it uses v$lock instead of gv$lock. It Seems like that:

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