I had an application from another group fail, and lock a DB row that I needed to use. In the investigation, a DBA showed me this code to figure out which rows were locked.
create table show_locks(id_num number);
declare
1x number;
begin
for i in 1..[max_id] loop
insert into show_locks values(i);
commit;
update [original_table] set [id] = i where id = i;
end loop;
end;
The update will throw an exception, and the max_id of the show_locks table will contain the id of the locked row. This statement can be run again by updating the 1..[max_id] to be [last_id+1]..[max_id].
I haven’t tried it, but I didn’t want to lose the idea either.
