Clever Geek Handbook

How can custom tables be locked in Oracle?

I have a trace from a machine where the following request seemed to hang for several days:

SELECT table_name FROM user_tables

What can create such a castle? Users cannot modify this table; and there were many subsequent instances of this request that succeeded.

+3


source to share


1 answer


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

However, in the future, if this or something similar happens again, you will want to use the Oracle wait interface. That is, take a look V$SESSION

.

First, you need to determine if the process is spinning (i.e. on the CPU) or blocking (i.e. waiting for a wait event). The way to determine this is to look at the column STATE

:



  • If STATE is 'WAITING'

    , the session is blocked. In this case, the EVENT column should describe what event the session is expecting.
  • If STATE is anything other than "WAITING", then the session is on the CPU and the EVENT column is the last thing it waited for.
    • If STATE is equal 'WAITED KNOWN TIME'

      , WAIT_TIME is the time to expect in seconds.
    • If STATE is 'WAITED SHORT TIME'

      , the session waited for less than one hundred seconds.
    • If STATE is equal 'WAITED UNKNOWN TIME'

      , then the expected time is unknown because the timed_statistics for the session was set to FALSE.

Hope it helps.

+3


source






More articles:


All Articles