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 :

User 1 :

SQL> update test set name=’lock’ where id=1;

1 row updated.

User didn’t commit here .

User 2 :
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

Another way to Lock :

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’;


SELECT c.owner,
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 :

Osama Mustafa

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.