Finding database lockers - Forms 12C
In the original iteration of our app (designed and run in Developer 6i - Client/Server mode) we provided a screen where users can list and locate other persons responsible for locking their record(s). To do so, we queried V$SESSION, v$LOCKS and DBA_OBJECTS among others to get a list of "blocking" locks and non-blocking locks with their lock duration and displayed them on our "Find Record Lockers" screen.
The original block of code that retrieved the locker's details was:
SELECT vs.username oracle_user,vs.osuser os_user, vs.machine machine, objs.object_name object_name,ROUND( locks.ctime/60, 2 ) lock_time_in_minutes, vs.serial# 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 tbls.owner = 'ADAM' AND objs.object_name = tbls.table_name and instr(tbls.table_name,'TRN') = 0 AND objs.owner != 'SYS' AND locks.TYPE = 'TM' order by lock_time_in_minutes DESC