REM locktracker.sql
REM
REM Copyright 2000, All Rights Reserved, Reed-Matthews, Inc.
spool locktracker.temp1
set feedback on;
set heading off;
set timing on;
column object_name format a30
column proc1 format 999999
column proc2 format 999999
column user1 format a10
column user2 format a10
column wait_time format 9999.99 
col v1 new_value v1
col locknum format 999999
col req_id format 9999999 
col program_name format a50
col uname format a10
REM set linesize 245 
set linesize 180 
prompt Delete past lock seekers that are no longer seeking.
delete from RMINC.RMINC_past_locks l
where not exists ( select 1
from 
   v$lock l1, v$process p1,  v$session s1,
   v$lock l2, v$session s, v$process p2
where p2.spid != p1.spid
and l1.lmode > 1
and p1.addr = s1.paddr
and s1.sid = l1.sid
and l1.id1 = l2.id1
and l2.sid = s.sid
and s.paddr = p2.addr
and s.lockwait is not null
and p2.spid = l.proc1
and p2.username = l.user1
and p1.spid = l.proc2
and p1.username = l.user2
and l2.id1 = l.object_id
)
/
commit;
prompt Take snapshot of running processes so can tie back to locks later.
insert into RMINC.RMINC_fcr_locktracker 
select * from apps.fnd_concurrent_requests 
  where phase_code = 'R' and status_code in ('I', 'R' ) 
/
prompt Get others so that each controlling manager is represented 
prompt (due to time delay). 
insert into RMINC.RMINC_fcr_locktracker 
select * from apps.fnd_concurrent_requests f1  
where f1.phase_code in ('I', 'P') and not exists 
( select NULL from RMINC.RMINC_fcr_locktracker v2 where 
    f1.controlling_manager +0= v2.controlling_manager) 
    and f1.controlling_manager +0 is not null 
/
prompt Insert lockseekers not yet in RMINC.RMINC_past_locks table
insert into RMINC.RMINC_past_locks 
select p2.spid proc1, p2.username user1, p1.spid proc2, 
  p1.username user2, sysdate time, l2.id1 object_id 
  from v$lock l1, v$process p1,  v$session s1, 
  v$lock l2, v$session s, v$process p2 where 
  p2.spid != p1.spid and l1.lmode > 1 
  and p1.addr = s1.paddr and s1.sid = l1.sid 
  and l1.id1 = l2.id1 and l2.sid = s.sid and 
  s.paddr = p2.addr and s.lockwait is not null 
  and ( p2.spid, p2.username, p1.spid, p1.username, 
  l2.id1) not in ( select proc1, user1, proc2, user2, 
  object_id from RMINC.RMINC_past_locks) 
/ 
commit; 
prompt Get rid of mirror-twin rows with this trick.  
delete from RMINC.RMINC_past_locks v1 where exists 
( select NULL from RMINC.RMINC_past_locks v2 where
v1.proc1 = v2.proc2 and v1.proc2 = v2.proc1 
and v1.user1 = v2.user2 and v1.user2 = v2.user1 
and v1.object_id = v2.object_id and v1.rowid > v2.rowid) 
/ 
commit; 
set term on 
prompt Show lock seekers that have been waiting past specified time 
select 'CONT' cont, proc1, user1, proc2, user2, 
o.name object_name, 
decode( o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 
3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 
11, 'PACKAGE BODY', 12, 'TRIGGER', 'UNKNOWNOBJECT'), 
round((sysdate - time) * 24, 2) wait_time from obj$ o, 
RMINC.RMINC_past_locks vpl where 
/* o.type = 2 and */ 
o.obj# = vpl.object_id and round((sysdate - time) * 24, 2) 
> &1 order by object_name, user1, user2, proc1, proc2 
/
prompt Create text for digging out concurrent process id for proc1. 
select distinct '$LOCKTRACKER/parent ' ||proc1||
'| sed -e "s/^\(.*\)$/insert into RMINC.RMINC_lock_ref values('||proc1||','||'\1'||');'||'/g"' 
from RMINC.RMINC_past_locks where user1 in ('applmgrt','applmgr','oracle') and 
round((sysdate - time) * 24, 2) > &1 
/ 
prompt Create text for digging out concurrent process id for proc2.  
select distinct '$LOCKTRACKER/parent ' ||proc2||
'| sed -e "s/^\(.*\)$/insert into RMINC.RMINC_lock_ref values('||proc2||','||'\1'||');'||'/g"' from RMINC.RMINC_past_locks where 
user2 in ('applmgrt','applmgr','oracle') and round((sysdate - time) * 24, 2) > &1 
/
spool off
!rm $LOCKTRACKER/locktracker.temp2
!grep "insert into" $LOCKTRACKER/locktracker.temp1 > $LOCKTRACKER/locktracker.temp2 
!rm $LOCKTRACKER/locktracker.temp3.sql 
!chmod 777 $LOCKTRACKER/locktracker.temp2 
!$LOCKTRACKER/locktracker.temp2 > $LOCKTRACKER/locktracker.temp3.sql 
delete from RMINC.RMINC_lock_ref; 
commit; 
@$LOCKTRACKER/locktracker.temp3.sql 
commit; 
spool locktracker.temp4
prompt Create text for digging out concurrent process id for LOCK_PROCESS_GRANDPA. 
select distinct '$LOCKTRACKER/parent ' ||LOCK_PROCESS_GRANDPA||
'| sed -e "s/^\(.*\)$/insert into RMINC.RMINC_lock_ref values('||LOCK_PROCESS||','||'\1'||');'||'/g"' 
from RMINC.RMINC_lock_ref
/ 
spool off
!rm $LOCKTRACKER/locktracker.temp5
!grep "insert into" $LOCKTRACKER/locktracker.temp4 > $LOCKTRACKER/locktracker.temp5 
!rm $LOCKTRACKER/locktracker.temp6.sql 
!chmod 777 $LOCKTRACKER/locktracker.temp5 
!$LOCKTRACKER/locktracker.temp5 > $LOCKTRACKER/locktracker.temp6.sql 
delete from RMINC.RMINC_lock_ref; 
commit; 
@$LOCKTRACKER/locktracker.temp6.sql 
spool locktracker.temp7
prompt Now cross reference to get the concurrent request_id  ! 
select distinct 'XREF', vlf.lock_process locknum, 
fcr.request_id req_id, fu.user_name uname, 
substr( fcp2.USER_CONCURRENT_PROGRAM_NAME, 1, 50 ) program_name 
from apps.fnd_user fu, apps.fnd_concurrent_programs_vl fcp2, 
apps.fnd_concurrent_processes fcp1, RMINC.RMINC_lock_ref vlf, 
RMINC.RMINC_fcr_locktracker fcr where fu.user_id = fcr.requested_by 
and fcp2.concurrent_program_id = fcr.concurrent_program_id 
and fcp2.application_id = fcr.program_application_id 
and fcr.controlling_manager = fcp1.concurrent_process_id 
and fcp1.os_process_id = vlf.lock_process_grandpa
and fcp1.concurrent_process_id in (select max(concurrent_process_id) -- Get latest record for OS pid
                                   from apps.fnd_concurrent_processes
                                   where os_process_id = vlf.lock_process_grandpa)
order by vlf.lock_process 
/ 
prompt Report back number of lock seekers waiting past specified time. 
select count(*) v1 from RMINC.RMINC_past_locks where 
round((sysdate -time) * 24, 2) > &1 
/ 
delete from RMINC.RMINC_fcr_locktracker; 
commit; 
prompt &v1 
exit &v1