is the locking Effect on Database performance ?
Yes . impede a transaction from finishing , since the Lock query Take long time running .
When the Locking Happened ?
I will Give you example :
Let assume that we have two Users Each Of them Update on the same table like the following :
SQL> update test set name=’lock’ where id=1;
1 row updated.
User didn’t commit here .
SQL> update test set name=’lock2′ where id=1;
User 2 will be waiting
performing a DDL (alter,create….) and get an ORA-00054 error.
ORA-00054: resource busy
to solve this issue
SQL> select object_id from dba_objectswhere owner=’Username’ and object_name=’Table’;
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.sid = a.session_id AND a.object_id = c.object_id
Refer also to :