Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Advanced
Jun 22, 2009

Determine the Session Waiting for a Shared Resource to be Released

Here's how to find out which session is waiting for a shared resource to be released:

Column host     format a6;
Column username format a10;
Column os_user  format a8;
Column program  format a30;
Column tsname   format a12;
 
select
   b.machine         host,
   b.username        username,
   b.server,
   b.osuser          os_user,
   b.program         program,
   a.tablespace_name ts_name,
   row_wait_file#    file_nbr,
   row_wait_block#   block_nbr,
   c.owner,
   c.segment_name,
   c.segment_type
from
   dba_data_files a,
   v$session      b,
   dba_extents    c
where
   b.row_wait_file# = a.file_id
and
   c.file_id = row_wait_file#
and
  row_wait_block#  between c.block_id and c.block_id + c.blocks - 1
and
  row_wait_file# <> 0
and
  type='USER'
;

The returned username will tell you the name of the user account waiting for a lock to be released at a block number.

Srinath MS
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap