Locking In Oracle

Locking in Oracle is one of the most common problem we will face as database administrator.

 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

Inforamtion about locks :
1-Locks scripts One.
2-Locks Scripts Two

performing a DDL (alter,create….) and get an ORA-00054 error.

ORA-00054: resource busy and acquire with NOWAIT specified

 to solve this issue

SQL> select object_id from dba_objectswhere owner=’Username’  and object_name=’Table’;

 OBJECT_ID
———-
 

SELECT c.owner,
 c.object_name,
 c.object_type,
 b.sid,
 b.serial#,
 b.status,
 b.osuser,
 b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.sid = a.session_id AND a.object_id = c.object_id
and a.object_id=;

Refer also to :
1-Locks

Osama Mustafa