Re: Cannot drop table in Oracle 8i

whyme_at_nowhere.com wrote in message news:<pfbdgu4dbntmorao5gat2vndcev1ptnndu_at_4ax.com>...
> I have created a table in Oracle 8i, and now I am not able to drip it.
>
> Here is the table I created:
>
> CREATE TABLE LOCATION
> (loc_id NUMBER(6),
> bldg_loc VARCHAR2(15),
> bldg_code VARCHAR2(20),
> room VARCHAR2(6),
> capacity NUMBER(5),
> CONSTRAINT location_loc_id_pk PRIMARY KEY (loc_id));
>
> When I type drop table location;
>
> I get a message :
>
> SQL> drop table location;
> drop table location
> *
> ERROR at line 1:
> ORA-00054: resource busy and acquire with NOWAIT specified
>
> What do I have to do in order to drop it? The table contains no data.
> I am not even able to drop it through DBA Studio.
>
> Thank You

Whyme, why not you. Sorry, I could not resist. But on to your question, the table you are attempting to drop is in use. Under the assumption that you do want to go ahead and drop this object you need to find out who (which session or sessions) is using the table and terminate their session so that Oracle can get a lock on the table and remove it.

You can issue a 'lock table location in exclusive mode' from one session and then follow the locking chain on a second session to locate the session your lock command is waiting on. Find out what they are doing and why. Terminate the lock holding session and then your other session will get the exclusive lock and you can use that session to issue the drop table.

There are a couple of articles on session to locks at the cooperative FAQ at http://www.jlcomp.demon.co.uk/faq/ind_faq.html

HTH -- Mark D Powell -- Received on Wed Jun 12 2002 - 08:28:05 CDT