When ever a person is updating a record in a table,others are experiencing the locks on that table. Also ,people who are not directly using that table are also experiencing the problem in saving their forms.
Right now, we are looking at who is locking the object and asking him/her to come out of the form.
Is there any parameter, which exclusively tells oracle to make it 'Row level locking'.
During updates oracle does do a row_level locking on the table. You can check this parameter ROW_LOCKING, it is by default set to ALWAYS, where oracle would obtain a row lock on the table on which it was updating. In your case, check the form application that is causing the table level lock and try to modify that application accordingly.
The question is 'what are the two sessions involved in the block doing?'
If, for example, the TOAD user runs an UPDATE, all the affected records will be locked until that use eventually gets around to doing a COMMIT. If an application user then tries to UPDATE one of these locked records, they will be stuck. This is completely expected.
However, I will assume that we are actually dealing with completely different records in the same or different tables. In that case, there are only 2 possibilities:
- There is a BITMAP index on the table.
- You have an FK indexing issue. If you don't have an index on an FK, then if someone changes a parent PK or deletes a parent, it will lock all the related rows in the child. See http://oradoc.photo.net/ora8doc/DOC/...1/ch6.htm#1574 for more info.