Oracle 10g sid

[tom quach_1]

Hi

if anyone knows how to get an oracle SID by using HP-UX process # as below:
PID=6314
by using
#lsof -p 6314 it does not tell much about any oracle SID
i am trying to relate Unix PID to and oracle SID but so far have not been able to.

Process Name PID PPID Pri Name ( 200% max) CPU IO Rate RSS Cnt
--------------------------------------------------------------------------------
oracleSUN2 6314 1 240 oracle 63.6/50.8 846.8 0.0/ 0.0 53.8mb 1

Thanks in advance.
Tom

[Eric Antunes]

Hi Tom,

From the process name I would tell that the instance SID is "SUN2".

Best Regards,

Eric

[Piergiacomo Perini]

Hi Tom,

maybe (in other Oracle version is this) "spid" from "v$process" is egual to PID.

hth
regards
pg

[Piergiacomo Perini]

_again_

with spid = to >PID you can find sid (session id) if you are refering to this and not to SID (system id, if i don't go wrong).

regards
pg

[Yogeeraj_1]

hi Tom,

try:
ps -ef|grep pmon

$ ps -ef|grep pmon
oracle 20327 1 0 Apr 12 ? 25:45 ora_pmon_sun2
oracle 11853 5046 0 13:22:44 pts/te 0:00 grep pmon
$

This is one easy way from which you can see that the SID=sun2

hope this helps too!
kind regards
yogeeraj

[Patti Johnson]

Tom,

Run this query from sqlplus.
select spid, s.sid, p.serial#, s.terminal, pid, machine, s.program, osuser,
p.program
from v$session s, v$process p
where addr = paddr and (spid like
'%&&OSID%' or process like '%&OSID%');

It will prompt you for the osid and respond with the SID and additional information about the session.

Patti

[Hein van den Heuvel]

pg, patti, How do you propose to connect to the right instance, if you don't know the SID?

Tom, what's behind the question?
It is is high resource consuming process?
The simple answer would seem to be to just strip 'oracle' fron the process name.
You might want to correlate that with the existance of a _pmon_ process.

fwiw,
Hein.

[Oviwan]

Hey

Which PID have you?

if it's the one of the pmon process use:

$ps -ef | grep ${PMON_PID} | grep -v grep | awk '{print substr($NF,10)}'

Regards

[Piergiacomo Perini]

Hi all,

Hein you are right , i assume that Tom knows the istance name / SID ...
but if this is not so then yogeeraj's (or eric's) method is good:
SID=SUN2.

hth
regards
pg

[tom quach_1]

Thank you all for your replies.
Sorry for the confusing. what i meant was the
Oracle process ID (SID from v$session)and not
oracle instance.
if i have PID from Unix command by using
#ps -ef|grep ora is there a way to trace this PID to oracle process ID?

Thanks,
Tom

[Sandman!]

>#ps -ef|grep ora is there a way to trace this PID to oracle process ID?

Do you mean the Oracle program/process that launched this shadow process. Since the process you are looking at is the Oracle server process aka shadow process which is forked by the Oracle listener process to service an incoming client connection. This will also be the process reported in the SPID column of the v$session table.

~hope it helps

[spex · Accepted solution]

Hi Tom,

Save the following script to a file, set the execute bit, and pass the OS pid of interest as the first argument:

#!/usr/bin/sh
if [ ${#} -lt 1 ]
then
echo "Usage: $(basename ${0}) "
exit 1
fi
# Set up Oracle environment
. oraenv
UN=myuser
PW=mypass
OSPID=${1}
sqlplus -S "${UN}/${PW}" << _EOF_
SELECT s.sid
FROM v\$session s, v\$process p
WHERE s.paddr = p.addr
AND p.spid=${OSPID};
exit;
_EOF_
exit 0

PCS

[tom quach_1]

Thanks Spex for the script.

this script works, However, i would like to know if these "ps" or "lsof" will work for the same purpose without using sqlplus.

Thanks,
Tom

[Sandman!]

No lsof or ps won't give the internal Oracle system identifier (SID) associated with each session. That can be obtained only using sqlplus.

~hope it helps

[tom quach_1]

Thank you all for your help.
Spex's script works for my question.

Thanks again
Tom