These scripts sql are valid for oracle 10g,11g and 12c:
DBMS_JOB FOR RAC :
select l.SID, s.SERIAL# ,j.job ,j.LOG_USER ,j.SCHEMA_USER ,j.WHAT,l.INST_ID ,S.LOCKWAIT ,S.SECONDS_IN_WAIT from dba_jobs j,gv$lock l,gv$session s where l.type='JQ' and l.ID2(+)=j.JOB and s.SID=l.SID |
DBMS_JOB FOR SINGLE INSTANCE :
select l.SID, s.SERIAL# ,j.job ,j.LOG_USER ,j.SCHEMA_USER ,j.WHAT ,S.LOCKWAIT ,S.SECONDS_IN_WAIT from dba_jobs j,v$lock l,v$session s where l.type='JQ' and l.ID2(+)=j.JOB and s.SID=l.SID |
ORACLE_SCHEDULER FOR SINGLE AND RAC INSTANCE:
SELECT owner, job_name, job_subname, status, run_duration, instance_id, session_id FROM all_scheduler_job_run_details ORDER BY 1 DESC |