Here are a few things you can do

• Kill session from GUI

Go to toad and log on as system administrator. You could also do this sql developer.

 

Now you would have an overview of the sessions that might involve in locking the tables you would like to drop. So you could simply identify the sessions and kill the corresponding sessions where table locks are withheld.

 

• Check v$session and kill session from command.

However sometimes, even we kill the existing sessions, the error would still not go away. I run my drop table script in toad that you could get the details of which table is being locked and the type of the lock. And then go to locks tab to identify the Sid number. You would see a similar window as below.  

 

For example, on the above screen, my PEGASYS_OBLIGATI table is locked with Lock Type DML. It is because I was doing the data refresh and then it was interrupted in the middle. But Sid 24 didn’t show in any of the sessions in the session window. I could not kill the session to get rid of the locks. Instead I run the following sql statement to find the serial number in order to kill the dead session that holds the lock for my table of object.

 

> select * from v$session where blocking_session is not null;

 

After running the sql statement, I find the serial number with the corresponding  sid.

 

> alter system kill session ‘42,789’;

 

Now we are all set. Lock is released and drop could be proceeding.

 

If you want  to know more about v$session, check the link below:

 

http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2088.htm