[Translated from mos] Get the session id of the running job, mosjob

[Translated from mos] Get the session id of the running job, mosjob
Obtain the session id of the job.

Reference Original:
How to get the session Id of the Running Job (Doc ID 1604966.1)

Applicable:
Oracle Database-Enterprise Edition-Version 9.2.0.8 and later
Information in this document applies to any platform.


Objectives:

How can I obtain the session id of a job running?

---> Note: The jobs mentioned in this article include those created by DBMS_JOB and DBMS_SCHEDULER.

Solution:

If a job is created using DBMS_JOB package, use the following statement:

Set feedback off
Alter session set nls_date_format = 'dd-MON-YYYY HH24: MI: ss ';
Set feedback on

Select jr. job, s. username, s. sid, s. serial #, p. spid, s. lockwait, s. logon_time
From dba_jobs_running jr, v $ session s, v $ process p
Where jr. sid = s. sid
And s. paddr = p. addr
Order by jr. job;



If the job is created using DBMS_SCHEDULER, use the following statement:
Set feedback off
Alter session set nls_date_format = 'dd-MON-YYYY HH24: MI: ss ';
Set feedback on

Select rj. job_name, s. username, s. sid, s. serial #, p. spid, s. lockwait, s. logon_time
From dba_scheduler_running_jobs rj, v $ session s, v $ process p
Where rj. session_id = s. sid
And s. paddr = p. addr
Order by rj. job_name;