查詢誰Lock住Oracle

標籤： Oracle

Oracle sid and Linux pid
為什麼會Lock呢？因為user在update table的時候，沒有做commit的動作，ORACLE就會把它Lock，而在一次在更改的時候，就會形成Wait狀態。

Sometimes we found Oracle process that has been running too long using top command on Linux. We know it has PID. But how to know the SID of the process?
Here is the script:

查詢Oracle誰Locks住Table:

select b.object_name obj_name,
d.ctime time,
to_char(d.lmode) l,
to_char(d.request) r,
a.os_user_name os_user,
c.machine machine,
c.program program,
c.serial# s_serial,
a.oracle_username ora_user,
a.object_id obj_id,
a.process pid,
a.session_id s_id,
p.spid
from v$locked_object a, all_objects b, v$session c, v$lock d,V$process p
where a.object_id=b.object_id
and c.Paddr = p.Addr
and a.session_id=c.sid
and a.session_id=d.sid
and d.type='TX'
order by d.ctime desc, p.spid

找出來之後，記住sid的編號，這個是oracle的sid，透過此sid(sid=<sid>)，可以找出是那一支process Lock住
相同的，知道Linux ID(pid=<pid>)，就可以找出這一個Process是誰了。

select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from
v$session b,
v$process a
where
b.paddr = a.addr
and type='USER'
and sid=<sid> <–記的要改掉
and pid=<pid><–記的要改掉
order by spid;

請ap處理這一件事或者到os去，可以刪除此PID

#kill -9 <pid_number>

解除Lock