ORA-00054: resource busy and acquire...

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Problem:
This error comes because some times two different sessions are performing DML tasks (delete, update and insert) on same column/table. This scenario some times leads to object locking in database.

Solution:

BEGIN
FOR I IN (SELECT S.SID SID, S.SERIAL# SERIAL FROM V$LOCKED_OBJECT L, V$SESSION S WHERE L.SESSION_ID = S.SID) LOOP
   EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION ''' || I.SID || ',' || I.SERIAL || '''');
END LOOP;
END;
/

Some Oracle Basic Queries

Parent Child relation:
to fetch parent/child tablename/columnname using table user_constraints and user_cons_columns

select  cc.column_name as column_name, c.table_name as table_name, rc.column_name as pcolumn_name, r.table_name as ptable_name
from    user_constraints c,
        user_constraints r,
        user_cons_columns cc,
        user_cons_columns rc
where   c.constraint_type = 'R'
and     c.constraint_name = cc.constraint_name
and     r.constraint_name = rc.constraint_name
and     c.r_constraint_name = r.constraint_name
and     cc.position = rc.position
and     c.table_name = 'child_table_name';