Unable to drop table; acquired NOWAIT

Occasionally, you will find that you cannot do something with a particular table, be it an insert, delete, or even a drop. Usually this is caused by a lock on that table. The way around this is to remove the session that has the lock.

In many cases, it may be best to just wait for the lock to go away on its own. This is particularly true if you are working on a busy database during production hours. However, there are times when you cannot wait, or you know that the process is not a user process and can be safely killed. To do this you would use the KILL SESSION syntax of the ALTER SYSTEM command.

What happens if you don"t know the offending session number?

If you have a known locked object, you can utilize the V$LOCKED_OBJECT dba view.

SELECT object_id,

session_id,
oracle_username,
os_user_name,
process,
locked_mode

FROM V$SESSION

If you are lucky, there will only be one object and one session, you can then use the session id and the object_id to get the remaining information. First, we need to verify that we're lookint at the right object

SELECT owner,

object_name,
object_type

FROM dba_objects
WHERE object_id = '%variable%'

(You would, of course, put the number you obtained from the V$LOCKED_OBJECT view in where I have put %variable%) Assuming the query returns the object name and type that you were expecting, we should go ahead and get the serial number of the locking process.

SELECT sid,

serial#

FROM V$SESSION
WHERE sid = '%sessionid%'

Again, replace the variable as appropriate.

Now, we have all the information we need to kill the troublesome session and get on with our day.

ALTER SYSTEM KILL SESSION '%sessionid%,%serial#%' IMMEDIATE

Note that you need BOTH seesionid and the serial# and that they are contained in single quotes and separated by a comma. Also note that IMMEDIATE is optional and is equivalent to a forced command. This command will release the lock on your object, allowing you to finish what you started.