Locking in Oracle and JDE

So you see that there is locking and you want to know what is going on in the database…

The following SQL is going to help with identifying what is being locked and blocked.

select session_id "sid",SERIAL#  "Serial",
substr(object_name,1,20) "Object",
  substr(os_user_name,1,10) "Terminal",
  substr(oracle_username,1,10) "Locker",
  nvl(lockwait,'active') "Wait",
  decode(locked_mode,
    2, 'row share',
    3, 'row exclusive',
    4, 'share',
    5, 'share row exclusive',
    6, 'exclusive',  'unknown') "Lockmode",
  OBJECT_TYPE "Type"
FROM
  SYS.V_$LOCKED_OBJECT A,
  SYS.ALL_OBJECTS B,
  SYS.V_$SESSION c
WHERE
  A.OBJECT_ID = B.OBJECT_ID AND
  C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc

Details of the process, get pid’s from above SQL:

select process as "PID:THREAD", server, ltrim(rtrim(program)), ltrim(rtrim(machine)), lockwait

from v$session where sid = 1504 or sid = 1176

This will give you the PID and thread ID of the E1 job that is holding the locks.  You can the use the following to kill the offending pids (or jde kernels).

Database level:

alter system kill session 'session-id,session-serial'

This command kills a session. The session-id and session-serialparameters are found in the v$session view (columns sid and serial#).

Operating System Level:

a) UNIX - I always locate the Server PID (SPID) from v$process and issue the UNIX .

ps –ef |grep SPID [to see the proc]

kill –9 SPID [to kill the proc]

b) The Windows command to kill this session would be as follows. This uses SID and thread as the parameters:

C:\oracle9i\bin>orakill ORCL92 768

I’m guessing that you might want to be careful with connection pooling if this is the case.