Re: What could be wrong with my DB if "drop table" hangs forever?

From: bdbafh <bdbafh_at_gmail.com>
Date: Mon, 11 Jun 2007 20:49:57 -0000
Message-ID: <1181594997.510771.201870@q75g2000hsh.googlegroups.com>

Ron Schnell wrote:
> Oracle 10g:
> I create a small table with:
> create table foo (bar number(10), bletch varchar(32));
> Insert one row:
> insert into table foo (bar, bletch) values (25, 'test');
> Then try to drop it:
> drop table foo;
>
> The command hangs forever (1 hour so far). I'm looking for areas to
> check for trouble (undo tablespace?). Any pointers to scripts I could
> use (as sysdba, if required) would be great.

Is this in SQL*Plus, or some other tool? If it is possible that an app server or some sort of broker is handling your statements, perhaps the insert statement (which is not committed) is in a different session than the drop table statement. If this is the case, than the drop table statement won't be processed until the row-level lock held by the insert statement is released.

lock tree diagram might help.
hang analyze might help.
system state dump (level 4) might help.

-bdbafh Received on Mon Jun 11 2007 - 15:49:57 CDT