Explanation
Dropping or truncating a table requires you to acquire an exclusive lock on the table. A table is a "busy" resource if there are other sessions modifying or holding a lock on the same table.
Steps:
1. Find session id, serial # of the session locking the table
select sid from v$access where owner='SCOTT' and object='EMP’;
select sid, serial#, command, taddr from v$session where sid=101;
2. Investigation into the validity of this session needs to be made.
Many times it may be a job that ran before or a hanging query. If it is determined that this session needs to be terminated, go to next step, or else wait until the user has completed the action.
3. To terminate the session:
alter system kill session 'sid, serial#’;
4. The session should now be killed and the lock SHOULD release.
Rechecking "v$access" will tell you this. If the lock does not immediately release, there may be a rollback occuring. To check this, goto next step, else dropping the table should work now.
5. To check for rollback:
select used_ublk from v$transaction where ADDR=taddr; # from v$session
If there is a value there, this is the number of undo blocks used by the transaction. Wait one minute and again
Note the value. If it is decreasing, a rollback is occuring and based on the difference between these values, you can "guesstimate" the time required to complete the rollback.
6. In some cases, you might need to kill the session at the OS level as well.Some 3rd party database monitoring software and certain unexplained situations will cause the lock to remain despite not showing up in "v$locked_object" after killing the session.
