Re: A table can not be access
Date: Mon, 24 Mar 2008 20:30:26 +0900
> 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.
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.