How can user_tables get locked on Oracle?

Question

I have a traceback from a machine where the following query seemed to get hung for days:

SELECT table_name FROM user_tables

What could possibly generate such a lock? Users can never modify this table; and there were plenty of subsequent instances of this query that ran successfully.

Answer 1

So, since the condition no longer exists, there's no way to tell what happened.

However, in the future, if this or something similar happens again, you'll want to use Oracle's wait interface. That is, look at V$SESSION.

First, you'll want to determine if the process is spinning (i.e., on CPU) or blocking (i.e., waiting on a wait event). The way to determine that, is to look at the STATE column:

• If STATE is 'WAITING', then the session is blocked. If that's the case, then EVENT column should describe what event the session is waiting on.
• If the STATE is something other than 'WAITING', then the session is on the CPU, and the EVENT column is the last thing that it waited on.
  • If STATE is 'WAITED KNOWN TIME', then WAIT_TIME is the time waited in centiseconds.
  • If STATE is 'WAITED SHORT TIME', then the session waited less than a centisecond.
  • If STATE is 'WAITED UNKNOWN TIME', then the time waited is not known because timed_statistics was set to FALSE for the session.

Hope that helps.