Tuesday, December 9, 2014

Lock on table (Oracle Apps)


Salam Alaikum,

   Many times we face the issue of lock on a table on the database.

   This could be huge problem, specially on Oracle Apps. As it can stop business process.

   I provide a part of code i use to know which is the table that locked, and additional information like session id (SID) thats locking the table. And other information like PID.

   This query gets information about the lock currently on the table: 
  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
      ,vlocked.session_id
FROM fnd_logins      fl
    ,fnd_user        fu
    ,v$locked_object vlocked
    ,v$process       vp
    ,v$session       vs
    ,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
and vs.type <> 'BACKGROUND'         -- very important to exclude background sessions
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status,'XX') != 'KILLED';
  
   And  then you can share the query output with your DBA (recommended), or you can kill the session that's locking the table by yourself (You have to be extra careful with the kill session command; it can cause an instance CRASH!!!).

   The code to kill a session is:

ALTER SYSTEM KILL SESSION '&SID,&serial#';

Hope you found it useful guys, feel free to leave a comment if have any note on the subject.

3 comments:

  1. I get a lot of great information from this blog. Thanks for sharing this valuable information to our vision. You have posted
    a trust worthy blog keep sharing.
    Oracle Apps Technical Online Training

    ReplyDelete
  2. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete