oracle有buffer等待事件，根据file#和block#确定objects

Wait until a buffer becomes available.
There are four reasons that a session cannot pin a buffer in the buffer cache, and a separate wait event exists for each reason:
1."buffer busy waits": A session cannot pin the buffer in the buffer cache because another session has the buffer pinned.
2."read by other session": A session cannot pin the buffer in the buffer cache because another session is reading the buffer from disk.
3."gc buffer busy acquire": A session cannot pin the buffer in the buffer cache because another session is reading the buffer from the cache of another instance.
4."gc buffer busy release": A session cannot pin the buffer in the buffer cache because another session on another instance is taking the buffer from this cache into its own cache so it can pin it.

查询等待时间对应的file#和block#：
select S.USERNAME,
       S.SID,
       s.sql_id,
       S.BLOCKING_SESSION,
       S.FINAL_BLOCKING_SESSION,
       S.P1TEXT,
       S.P1,
       S.P2TEXT,
       S.P2,
       S.P3TEXT,
       S.P3,
       S.EVENT,
       s.WAIT_CLASS,
       S.SECONDS_IN_WAIT
  from v$session S
 where S.event = 'buffer busy waits';

根据file# 和 block#找到objects id
select objd, file#, block#, class#, ts#, cachehint, status, dirty
  from v$bh
 where file# = 440
   and block# = 117877;

最后找到对应的对象：
select * from dba_objects where data_object_id = 83842;

整合后的SQL为:
select S.USERNAME,
       S.SID,
       s.sql_id,
       S.BLOCKING_SESSION,
       S.FINAL_BLOCKING_SESSION,
       S.P1TEXT,
       S.P1,
       S.P2TEXT,
       S.P2,
       S.P3TEXT,
       S.P3,
       S.EVENT,
       s.WAIT_CLASS,
       S.SECONDS_IN_WAIT,
       O.OWNER,
       O.OBJECT_NAME
  from v$session S, V$BH B, DBA_OBJECTS O
 where S.USERNAME = 'DEVMGR'
   AND S.event = 'buffer busy waits'    
   AND B.file# = S.P1
   and B.block# = S.P2
   AND B.OBJD = O.DATA_OBJECT_ID;