Donald K. Burleson Oracle Tips

Finding the cause of hung end-user sessions within Oracle

It is not uncommon for an end-user session to “hang” when they are trying to grab a shared data resource that is held by another end-user.  The end-user often calls the help desk trying to understand why they cannot complete their transaction, and the Oracle professional must quickly identify the source of the contention.

Whenever Oracle is has a session waiting on a resource, this information can be found in the v$session view in the row_wait_file# and row_wait_block#. 

The file number and block number can then be cross-referenced into the dba_extents view to see the name of the table where the session is waiting on a block.

Here is the script.  Note how the v$sessionrow_wait_file# is joined into the dba_extents view.

Here is the output from this SQL*Plus script:

BOX    USERNAME   SERVER    OS_USER  PROGRAM

------ ---------- --------- -------- ------------------------------

TS_NAME        FILE_NBR  BLOCK_NBR OWNER

------------ ---------- ---------- ------------------------------

SEGMENT_NAME

---------------------------------------------------------------------------

SEGMENT_TYPE

-----------------

avmak1 JONES        DEDICATED server      ?  @avmak1 (TNS interface)

IRMS_D                9      70945 SYSADM

CUSTOMER_VIEWS40

TABLE

Here we see that a session owned by user “JONES” is waiting for a resource in the IRMS_D tablespace at data block number 70945.  At this Oracle data block we find the CUSTOMER_VIEWS40 table.

Now that you have identified the source of the contention, you can then locate other users who may be holding locks on this table.  This type of wait can commonly be issued when a large update task is holding locks on the table, or when an individual task has placed an exclusive lock on specific rows in the table.