We help IT Professionals succeed at work.

Table lock and 'un index foreign key constraint’ related issues on oracle 11g

I wonder what it mean by below issue based on the oracle 11g database report
Table lock on aaa _bbb table  ‘un index foreign key constraint’ related issues. It forced to restart all production jvms and close the database connections and restablish them which blocked all the production users for couple of hours. Why table lock happens. what are root causes to debug and how to avoid them our application written mainly on java j2ee using other technologies as well
Please advise.
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2012
Commented:
Root cause:

If you try to modify the parent record, it will lock the child tables while it enforces/validates the constraint.

If you have an index on the constrained columns of the child table, then only the affected rows will be locked.

Fix:

Create an index on the child table's constrained columns

Author

Commented:
Does re ordering or mismatch or dropping the indexes has any impact which could have caused the issue? please advise
Most Valuable Expert 2011
Top Expert 2012

Commented:
dropping the indexes would definitely cause it