Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12399
  • Last Modified:

find session id from unix process id in oracle


i ran one database script from os(unix) using nohup. i got unix process id. now i want to check the status of
my script in the database. how do i check it.

i want to find oracle session id based on this process id. how do i know this
1 Solution
I believe there is no connection between unix process id and oracle process id.
However, based on user id you and instance name you can get the info by querying v$session view
Well, first, which unix process id do you have?  Of your client program (i.e. SQL*Plus, Pro*C program, etc) or of your server process (i.e. oracle{your_ORACLE_SID} )??

If you have the client program pid, do:
ps -ef|grep pid, and look for a program that has that pid as the parent id.

Here's an example:
Here, my pid of my SQL*Plus session is 7306, so I do:
ps -ef|grep 7306
and I see this:
oracle    7306 30614  0 16:01 pts/3    00:00:00 sqlplus
oracle    7307  7306  0 16:01 ?        00:00:00 oracleinprd1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7440 31625  0 16:20 pts/3    00:00:00 grep 7306
oracle   31622  7306  0 16:15 pts/3    00:00:00 /bin/ksh -c sh

So, my server process is 7307.

Now, going to SQL*Plus, I can take that, and see the following:
  1* select vs.sid,vs.serial#,vs.username,vs.osuser,vs.machine,vs.program from v$session vs, v$process vp where vs.paddr = vp.addr and vp.spid = 7307
VMS@inprd1> /

---------- ---------- ------------------------------
       116      56847 VMS
sqlplus@rac01.prod.il.pqe (TNS V1-V3)

So, v$process.spid is the background process, and it connects from v$process to v$session via v$process.addr=v$session.paddr.

Once you have the SID, you can look at V$SESSION, V$SESSION_WAIT, V$SESSTAT, etc, to monitor the session.

Hope that helps,


Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now