-- iDempiere 去抓取 Oracle EBS 找出被鎖死的程序 v$locked_object

-- iDempiere 去抓取 Oracle EBS 找出被鎖死的程序 v$locked_object

SELECT vs.USERNAME, lo.OBJECT_ID, sob.NAEM, lo.SESSION_ID, vs.SERIAL#, lo.ORACLE_USERNAME, lo.OS_USER_NAME, lo.PROCESS
FROM V$LOCKED_OBJECT lo
INNER JOIN V$SESSION vs ON lo.SESSION_ID = vs.SID
INNER JOIN SYS.OBJ$ sob ON sob.obj# = lo.OBJECT_ID;

-- iDempiere 去抓取  Oracle EBS 送出解開被鎖死的程序 Interrupt the process deadlock,

ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

SELECT c.owner, c.object_name, c.object_type,  fu.user_name  locking_fnd_user_name,
fl.start_time locking_fnd_user_login_time,
vs.module,  vs.machine, vs.osuser,vlocked.oracle_username,
vs.SID, vp.pid, vp.spid AS os_process,
vs.serial#, vs.status, vs.saddr, vs.audsid, vs.process
           FROM v$locked_object vlocked
     INNER JOIN dba_objects c ON vlocked.object_id = c.object_id
     INNER JOIN v$session  vs ON vs.SID = vlocked.session_id
     INNER JOIN v$process  vp ON vs.paddr = vp.addr
LEFT OUTER JOIN fnd_logins fl ON vp.spid = fl.process_spid  AND vp.pid = fl.pid
LEFT OUTER JOIN fnd_user   fu ON fl.user_id = fu.user_id
WHERE 1=1
  AND NVL (vs.status, 'XX')<> 'KILLED';