Which Process ID?

Hi,

In this story, I am going to tell you a question that itching my mind lately. Here is a SQL code for listing locked tables (thanks to Tuğrul Tekin Doyuk):

select distinct se.inst_id,
 se.SID,
 p.SPID “Server PID”,
 se.USERNAME “ORA_USER”,
 se.OSUSER “OS_USER”,
 se.MACHINE,
 se.PROGRAM,
 se.last_call_et,
 o.object_type || ‘ : ‘ || o.object_name “AKTIFSON”,
 decode(lo.LOCKED_MODE,
 1,
 ‘No Lock’,
 2,
 ‘Row Share’,
 3,
 ‘Row Exclusive’,
 4,
 ‘Share’,
 5,
 ‘Share Row Exclusive’,
 6,
 ‘Exclusive’,
 ‘NONE’) “LOCK Type”,
 ‘alter system kill session ‘’’ || se.SID || ‘,’ ||
 se.SERIAL# || ‘,@’ || se.INST_ID || ‘’’ immediate;’ “ORA KILL SQL”
 from gv$locked_object lo, dba_objects o, gv$session se, gv$process p
where lo.OBJECT_ID = o.object_id
 and se.PADDR = p.ADDR
 and se.SID = lo.SESSION_ID
 and lo.inst_id = se.inst_id
 and lo.inst_id = p.inst_id
 order by LAST_CALL_ET DESC;

When I try to rewrite this code I read the definition of dictionary views. The problem starts here. According to the description of V$PROCESS “SPID” column shows us “Operating system process identifier”. Also, definition of V$LOCKED_OBJECT tells us “PROCESS” column contains “Operating system process ID”. So, I thought I can use these two columns to join the tables. Yet, when I tried to join the tables like this:

from gv$locked_object lo, dba_objects o, gv$session se, gv$process p
where lo.OBJECT_ID = o.object_id
 and se.PADDR = p.ADDR
 and se.SID = lo.SESSION_ID
 and lo.inst_id = se.inst_id
 and lo.inst_id = p.inst_id
 and lo.process = p.spid

the script does not work because of “lo.process = p.spid” join point. Because, they are completely different(obviously!!!)(my last_call_et was high, I had to do something else :) ).

Let’ s dig a little bit more. The description of V$LOCKED_OBJECT:

SELECT x.inst_id,
 x.kxidusn,
 x.kxidslt,
 x.kxidsqn,
 l.ktadmtab,
 s.indx,
 s.ksuudlna,
 s.ksuseunm,
 s.ksusepid,
 l.ksqlkmod,
 x.con_id
 FROM x$ktcxb x, x$ktadm l, x$ksuse s
 WHERE x.ktcxbxba = l.kssobown AND x.ktcxbses = s.addr;

I was looking for “ s.ksusepid” and at the end found Pavan DBA’s Blog. “s.ksusepid” column contains “client” s process id and this made things more clear.

Apart from the question till this point, did I miss something or the document of V$LOCKED_OBJECT needs an update?

I have another question for you. My client are listed below:

The process ids of Sqldeveloper and sqlplus were respectively “3455” and “18052”, they were just fine, but process id of Toad was “11668:17176”. When I checked my Windows task manager for Toad’ s process id, it only showed me main process id, which is “11668”. Debugging with Windows own tools did not give me any particular answers, so I do not know where “17176” comes from. I thought it might be because of threading mode of Toad, but it was the same after turning off the threading mode. Do you know why or have any idea?

If there is something wrong or missing please let me know!

Database Version: Oracle 19c Enterprise Edition — 19.3.0.0.0 (29585399)

Thanks!