[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;