Queries Arsenel for Oracle Apps DBA - Part 4

Queries Arsenel for Oracle Apps DBA - Part 4

**********************************************************

User getting locking errors in Application:- Identify User locks

**********************************************************

--Locate User-locks

--Find the username from Incident/or check from user



select user_name,description from fnd_user where user_name in ('001955');



--Below query will help you finding active sessions of the User along with FRM session it is using.



select status,sid,serial#,action from v$session where action like '1955%';



--Find if any of users session is visible in v$locked_object



SELECT lo.session_id "SID",

lo.object_id,

ob.owner,

ob.object_name,

ob.object_type

FROM v$locked_object lo,

dba_objects ob

WHERE lo.object_id = ob.object_id



and lo.session_id in ('3605','3645','3609','3794');



--If that is the case then confirm from name from user and kill the session



--Also below query can be used to associate user name with Form session



select vs.action, fft.user_form_name, vs.status session_status, vs.sid

From v$session vs, applsys.fnd_form ff,

applsys.fnd_form_tl fft

where vs.module like ff.form_name

and ff.FORM_ID = fft.form_id

and ff.application_id = fft.application_id

and vs.action like 'FRM1955%'



SELECT s.saddr, s.sid, s.serial#, s.username, s.osuser, s.machine, s.program,

s.logon_time, s.status, p.program, p.spid FROM v$session s, v$process p

WHERE s.paddr = p.addr AND s.sid IN ('3828');



After identifying and analysing session, corrective action can be taken



-- Other Query to find Which FND_USER is locking that 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

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 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';