Technical‎ > ‎Scripts‎ > ‎

Script: To find out which Oracle e-business user is locking a table


The following query will help you identify the fnd user/ oracle e-business user that is locking a row/table.


SELECT objects.owner,

       objects.object_name,

       objects.object_type,

       user1.user_name         locking_fnd_user_name,

       login.start_time        locking_fnd_user_login_time,

       vs.module,

       vs.machine,

       vs.osuser,

       vlocked.oracle_username,

       vs.sid,

       vp.pid,

       vp.spid                 os_process,

       vs.serial#,

       vs.status,

       vs.saddr,

       vs.audsid,

       vs.process

  FROM fnd_logins      login,

       fnd_user        user1,

       v$locked_object vlocked,

       v$process       vp,

       v$session       vs,

       dba_objects     objects

 WHERE vs.sid = vlocked.session_id

   AND vlocked.object_id = objects.object_id

   AND vs.paddr = vp.addr

   AND vp.spid = login.process_spid(+)

   AND vp.pid = login.pid(+)

   AND login.user_id = user1.user_id(+)

--change the table name below

   AND objects.object_name LIKE '%' || upper('PO_HEADERS_ALL') || '%'

   AND nvl(vs.status,

           'XX') != 'KILLED';


Free Hit Counters
Comments