How to trace by table

Hi,

Our legacy system is using a single row single column table for common sequence for all tables for the app.

create table commen_sequence (id, number, val number);

Each user session will do something like this to get sequence value.

select * from common_sequence where id = 1 for update nowait

if successful then

  update common_sequence set val = val + 1 where id = 1;

end if

For a "good " reason management do not want to replace the logic with a Oracle sequence.

Supposedly the lock held on common_sequence table should be very short. But sometime we see Ora-0054 resource busy on common_sequence.