Did you ever get an email or a phone call from a developer telling you they are getting an error when trying to drop a table? It sounds something like this:

“What is going on, I cannot drop table A, I am getting an error:”

SQL> drop table HR.EMPLOYEES;
drop table HR.EMPLOYEES
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The mistake that some of us do initially, is to look for blocking sessions in the database. But the above error that the developer is getting, most likely has nothing to do with blocking. Rather it has all to do with locking.

Why do we have locks in the database?

The database locks are in place to prevent incorrect updates of the data, or incorrect alters of the underlying object structures, while multiple transactions are accessing the shared data in the database. Locks are there to help maintain the database consistency and integrity.

There are two main categories of locks:
-exclusive locks
-shared locks

There is only one exclusive lock that can be obtained on a table or row at a time.
The exclusive locks prevents the resource to be shared. An exclusive lock is obtained when the session modifies the data.
The transaction that locks the resource in exclusive mode is the only one that can modify the resource, until the exclusive lock is released.

There are many shared locks that can be obtained on a table or row. The sessions that read data usually get a shared lock on the resource.

Oracle is trying to lock the resource at the lowest level possible. There are locks at the row level and there are locks at the table level.

When you want to modify data (update one row of a table), Oracle is locking exclusively that one row, and is locking the table in shared mode.

Other sessions can access the data (read), modify other rows in the table, but cannot alter the structure of the table, or drop the table.

When someone is altering the resource (table), then an exclusive table lock is obtained. If another session has already an exclusive lock on the resource, then the exclusive table lock cannot be obtained.

Now that the locking has been understood, the error message the developer was getting is more clear.

What you need to do to help the developer out, is to find who is holding the lock, and in what mode.

Once that information is known, you have 2 options:
– ask the “locking” user to release the lock
– kill the “locking” user’s session.

Below is a script to provide you the answer to the question: Who is locking the table?

col object for A30
col type for A12
col serial# for 999999999
col osuser for A15
col lock_mode for A25
col username for A15

select
   c.owner || '.' ||  c.object_name object,
   c.object_type as type,
   DECODE(a.locked_mode, 0, NONE
           ,  1, '1 - Null'
           ,  2, '2 - Row Share Lock'
           ,  3, '3 - Row Exclusive Table Lock.'
           ,  4, '4 - Share Table Lock'
           ,  5, '5 - Share Row Exclusive Table Lock.'
           ,  6, '6 - Exclusive Table Lock'
           ,  locked_mode, ltrim(to_char(locked_mode,'990'))) lock_mode,
   b.inst_id as node,
   b.sid,
   b.serial#,
   b.status,
   b.username,
   b.osuser
from
   gv$locked_object a ,
   gv$session b,
   dba_objects c
where b.sid = a.session_id
and   a.object_id = c.object_id
and   a.inst_id=b.inst_id;

--Sample Output

OBJECT       TYPE  LOCK_MODE                     node  sid  SERIAL# STATUS   USERNAME OSUSER  
------------ ----- ----------------------------- ----- ---- ------- -----    -------  ------
HR.EMPLOYEES TABLE 3 - Row Exclusive Table Lock.    1  397  26193   INACTIVE HRAPP    drobete