doc

    Name:    objwait.sql

    Author:  Mark Gurry

    This script lists all objects that the waits are currently occurring 
    on.

#

set heading off
set pagesize 999
set verify off
set feedback off
set echo off
column aa newline
spool ext2.sql
column nl newline
select 'doc ' nl,
       'User '||v$session.username||'('||v$session_wait.sid||')' nl,
       v$sqltext.sql_text nl,
       '#' nl,
       'select segment_name, segment_type ' nl,
       'from dba_extents ' nl,
       'where file_id='||v$session_wait.p1 nl,
       '  and '||v$session_wait.p2||' between block_id and block_id + blocks -1);'
  from v$session, v$sqlarea, v$session_wait
 where (v$session_wait.event  like '%buffer%'
                         OR
        v$session_wait.event  like '%write%'
                         OR
        v$session_wait.event  like '%read%')
   and v$session_wait.sid    = v$session.sid
   and v$session.sql_address = v$sqlarea.address
   and v$session.sql_hash_value = v$sqlarea.hash_value
/

spool off
@ext2.sql