TO LIST ALL LOCKED OBJECTS:
SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)', b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
-------------------------------------
SELECT a.type,
Substr(a.owner,1,30) owner,
a.sid,
Substr(a.object,1,30) object
FROM v$access a
WHERE a.owner NOT IN ('SYS','PUBLIC')
ORDER BY 1,2,3,4;
-------------------------------------
TO LIST LOCKED OBJECTS FOR RAC:
SELECT b.inst_id,
b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
gv$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;
------------------------------------
R12 Long Running Concurrent Jobs More Than 5 Mins
SELECT DISTINCT fcpt.USER_CONCURRENT_PROGRAM_NAME,
(fcr.actual_completion_date-fcr.actual_start_date)*24*60*60 process_time,
fcr.request_id,
fcr.parent_request_id,
fcr.request_date,
fcr.actual_start_date,
fcr.actual_completion_date,
(fcr.actual_completion_date-fcr.request_date)*24*60*60 end_to_end,
(fcr.actual_start_date-fcr.request_date)*24*60*60 lag_time,
fu.user_name,
fcr.phase_code,
fcr.status_code,
fcr.argument_text,
fcr.priority
FROM apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs fcp,
apps.FND_CONCURRENT_PROGRAMS_TL fcpt,
apps.fnd_user fu
WHERE fcr.concurrent_program_id=fcp.concurrent_program_id AND
fcp.concurrent_program_id=fcpt.concurrent_program_id AND
fcr.requested_by=fu.user_id AND
status_code='C' AND
(fcr.actual_completion_date-fcr.actual_start_date)*24*60*60 > 300
ORDER BY Process_time desc;
-----------------------------------------
R12 EBS USER LOCKING 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(+)
AND objects.object_name LIKE '%' || upper('PO_HEADERS_ALL') || '%'
AND NVL (vs.status, 'XX') != 'KILLED';
------------------------------------
R12 EBS To Check Non-Blocking Objects
SELECT oracle_username || ' (' || s.osuser || ')' username,
s.sid || ',' || s.serial# sess_id,
owner || '.' || object_name object,
object_type,
DECODE (l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global')
status,
DECODE (v.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lmode))
mode_held
FROM v$locked_object v,
dba_objects d,
v$lock l,
v$session s
WHERE v.object_id = d.object_id
AND v.object_id = l.id1
AND v.session_id = s.sid
-- AND l.block <> 0
ORDER BY oracle_username, session_id;
------------------------------------
R12 GET SID, SERIAL#, SPID OF A RUNNING CONCURRENT REQUEST
SELECT fcr.request_id,
vs.sid,
vs.serial# ,
vp.SPID
FROM apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_processes fcp,
v$process vp,
v$session vs
WHERE fcr.controlling_manager = fcp.concurrent_process_id
AND vp.pid = fcp.oracle_process_id
AND fcp.session_id = vs.audsid
AND fcr.request_id = &Request_ID
AND fcr.phase_code = 'R';
-------------------------------------------
ORACLE APPS RECORD LOCKING
FRM-40501: COULD NOT RESERVE RECORD [2 TRIES]
One of your end users
attempts to modify a record, and the user is prompted with a dialog box asking
“Could not reserve record [2 tries]. Keep trying?” The user answers “yes” and after
being prompted a few more times the user gives up. Ultimately the user gets a
“FRM-40501: ORACLE error: unable to reserve record for update or delete.” When
this happens it’s nice to have some scripts ready to go to quickly diagnose
what is causing the contention, so appropriate action can be taken. The forms
don’t wait to the obtain the lock for very long, so it’s a little more
difficult to determine the blocking lock. This first script provides a listing
of the possible locks and some relevant E-Business Suite information for
digging further.
Query 1:
SELECT vs.audsid audsid,
locks.sid sid,
vs.serial# serial#,
vs.username oracle_user,
vs.osuser os_user,
vs.program program,
vs.module module,
vs.action action,
vs.process process,
DECODE (locks.lmode, 1, NULL, 2, 'Row Share',
3, 'Row Exclusive',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive', 'None') lock_mode_held,
decode(locks.request,
1, NULL,
2, 'Row Share',
3, 'Row Exclusive',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive', 'None') lock_mode_requested,
decode(locks.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Log Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
locks.type) lock_type,
objs.owner object_owner,
objs.object_name object_name,
objs.object_type object_type,
round( locks.ctime/60, 2 ) lock_time_in_minutes
from v$session vs,
v$lock locks,
dba_objects objs,
dba_tables tbls
where locks.id1 = objs.object_id
and vs.sid = locks.sid
and objs.owner = tbls.owner
and objs.object_name = tbls.table_name
and objs.owner != 'SYS'
and locks.type = 'TM'
order by lock_time_in_minutes;
To determine the
table(s) the form is trying to lock, use the Help->Record History menu
option; this provides the base table or view for the form block.
Look through the
result set from Query 1 for an object_name (typically a table or view) in the
same vicinity as your table or view. If you have a form block based on a view,
it may be helpful to look up the tables behind the view. If the action starts
with an ‘FRM:%’, then another forms session has the lock.
One thing to note:
You’ll often see the same user blocking themselves. This could be a training
issue, or it could be due to a previous forms session that crashed, but the
f60webmx process did not die. If this is the case, you can kill the application
server OS process (based on the process value in Query 1).
Query 2 provides
further details for results in Query 1 that are forms sessions – simply plop in
the AUDSID from Query 1.
Query 2:
SELECT F.AUDSID,
S.SID,
S.SERIAL#,
L.USER_ID,
L.TERMINAL_ID,
L.LOGIN_NAME,
R.RESP_APPL_ID,
R.RESPONSIBILITY_ID,
F.FORM_ID,
F.FORM_APPL_ID,
L.PID,
L.PROCESS_SPID,
NVL (F.START_TIME, NVL (R.START_TIME, L.START_TIME)) TIME,
USR.USER_NAME,
a.application_name,
RSP.RESPONSIBILITY_NAME,
FRM.USER_FORM_NAME,
s.program,
s.action,
s.module,
s.state,
s.event,
s.wait_class,
s.seconds_in_wait
FROM FND_RESPONSIBILITY_TL RSP,
FND_FORM_TL FRM,
FND_USER USR,
FND_LOGINS L,
FND_LOGIN_RESPONSIBILITIES
R,
FND_LOGIN_RESP_FORMS F,
GV$SESSION S,
fnd_application_tl A
WHERE F.AUDSID = &ENTER_FORM_AUDSID
AND R.LOGIN_ID = F.LOGIN_ID
AND R.LOGIN_RESP_ID = F.LOGIN_RESP_ID
AND L.LOGIN_ID = R.LOGIN_ID
AND L.END_TIME IS NULL
AND R.END_TIME IS NULL
AND F.END_TIME IS NULL
AND L.USER_ID = USR.USER_ID
AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
AND R.RESP_APPL_ID = RSP.APPLICATION_ID
AND RSP.LANGUAGE = 'US'
AND RSP.application_id = a.application_id
AND a.language = 'US'
AND F.FORM_ID = FRM.FORM_ID
AND F.FORM_APPL_ID = FRM.APPLICATION_ID
AND FRM.LANGUAGE = 'US'
AND F.AUDSID = S.AUDSID;
If a concurrent
program holds the lock, Query 3 provides a bit more information. Here we can
see the user, concurrent program, how long it’s been running and log/output
files.
Query 3:
SELECT fcr.request_id,
fcr.requested_by,
fu.user_name,
fcr.program_application_id,
fcr.concurrent_program_id,
fcr.actual_start_date,
fat.application_name,
fcp.concurrent_program_name,
fcpt.user_concurrent_program_name,
fcr.description,
fcr.logfile_node_name,
fcr.outfile_name,
fcr.logfile_name,
fcr.completion_text,
fcr.parent_request_id,
vs.process,
vs.state,
vs.event,
vs.wait_class,
vs.seconds_in_wait
FROM v$session vs,
fnd_concurrent_requests fcr,
fnd_application_tl fat,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl
fcpt,
fnd_user fu
WHERE vs.audsid = &ENTER_CONC_PROCESS_AUDSID
AND vs.process = fcr.os_process_id
AND fcr.actual_completion_date IS NULL
AND fcr.program_application_id = fat.application_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcpt.application_id
AND fcr.concurrent_program_id = fcpt.concurrent_program_id
AND fcr.requested_by = fu.user_id;
This should be enough
information to chase down the offender (someone out for coffee and not save
that latest change first?) or possibly even point to a process that needs
attention.
