Re: A table can not be access

From: 조동욱 <ukja.dion_at_gmail.com>
Date: Mon, 24 Mar 2008 20:30:26 +0900
Message-ID: <43c2e3d60803240430v8d2a3d3md3334de4132bc7fa@mail.gmail.com>


> You may just be wating a while.
> Look in longops for whether your process is taking up time. Have a look at
V$SQLAREA for your drop statement.

Positive.

My guess is that you're suffering library cache lock contention. 1. Session#1 tries to drop constraint on the table. Say that it takes 1 hour by any reason.
2. Session#1 holds library cache lock in exclusive mode on the table during that 1 hour.
3. Session#2 tries to execute SQL statements on that table. Any session that needs to access table needs library cache lock on that table. 4. But session#2 is blocked just because session#1 is holding library cache lock in exclusive mode.

You need to check v$session_wait view.
Which wait event do you see for the blocked session(sql*plus in your example)?
Posting the result of query on v$session_wait would be quite informative.

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 24 2008 - 06:30:26 CDT

Original text of this message