Objects found in Oracle based on file# and block#

We often see the following information in trace files produced in 10046. Represents the 8 blocks that the system begins when a block is waiting for a hash to read a file number.

 wait #6: nam= ' db file scattered read ' ELA = 438472 file#=6 block#=2641 blocks=8wait #6: nam= ' db file scattered read ' ela= 1039 file#=6 block#=833 blocks=8 obj#=9005 4 tim=878243950382wait #6: nam= ' db file scattered read ' ela= 835 file#=10 block#=22961 blocks=8 obj#=90054 tim=87824395716 8WAIT #6: nam= ' db file scattered read ' ela= 815 file#=11 block#=7409 blocks=8 obj#=90054 tim=878243966696 ...  
  
 
  There are three main ways now  
 
 Span style= "font-family:comic sans ms,sans-serif; font-size:12px; " >1. Query dba_extents  table,  poor efficiency .  

       Select Segment_name from dba_extents where and rownum = 1 and file_id = 6 and 2641 between block_id and block_id + blocks -1;

00:02:43.84 Statistics----------------------------------------------------------       4676  recursive calls          2  db Block gets    4077424  consistent gets       6492  Physical reads          0  redo Size        418  Bytes sent via sql*net to client        -  bytes received via sql*net from client          2  sql*net roundtrip S To/from client          5  sorts (memory)          0  sorts (disk)          1  rows processed

2. Query v$bh view, query quickly,

sql> desc V$BH
is the name empty? type
----------------------------------------- -------- ----------------------------
file# Number
block# Number
class# Number
STATUS VARCHAR2 (Ten)
XNC Number
forced_reads Number
forced_writes Number
lock_element_addr RAW (4)
lock_element_name Number
Lock_element_class Number
DIRTY VARCHAR2 (1)
TEMP VARCHAR2 (1)
PING VARCHAR2 (1)
STALE VARCHAR2 (1)
DIRECT VARCHAR2 (1)
NEW CHAR (1)
OBJD Number
ts# Number
lobid Number
cachehint Number

Sql> Select Objd, File#,block#,class#,ts#,cachehint,status,dirty from V$BH where file#=2 and block#=59101;

OBJD file# block# class# ts# cachehint STATUS DI
---------- ---------- ---------- ---------- ---------- ---------- -------------------- --
6373 2 59101 1 1 xcur N

3. Dump the block to find the object ID.

Alter system dump DATAFILE 6 block 2641;

File Which_obj2:

Define __file = &1
Define __block = &2
alter system dump DATAFILE &__file block &__BLOCK;
Set Serveroutput on

Declare
v_dbavarchar2 (+);
v_typevarchar2 (+);
V_obj_idnumber;
v_obj_namevarchar2 (+);
begin
For R in (select Column_value as-t from table (GET_TRACE_FILE1)) Loop
if Regexp_like (r.t, ' buffer TSN: ') Then
dbms_output.put_line ('------------------------------------------------');
V_DBA: = Regexp_substr (r.t, ' [[:d igit:]]+/[[:d igit:]]+ ');
dbms_output.put_line (rpad (' DBA = ', ') | | v_dba);
End If;

if Regexp_like (r.t, ' type:0x ([[: xdigit:]]+) = ([[:p rint:]]+) ') Then
V_type: = substr (Regexp_substr (r.t, ' =[[:p rint:]]+ '), 2);
dbms_output.put_line (rpad (' type = ', ') | | v_type);
End If;

if Regexp_like (r.t, ' seg/obj: ') Then
v_obj_id: = To_dec (substr (Regexp_substr (R.T,
' seg/obj:0x[[:xdigit:]]+ ' ));
Select object_name to V_obj_name from All_objects
where data_object_id = v_obj_id;
dbms_output.put_line (rpad (' object_id = ', ') | | v_obj_id);
dbms_output.put_line (rpad (' object_name = ', ') | | v_obj_name);
End If;

if Regexp_like (r.t, ' OBJD: [[:d igit:]]+ ') Then
v_obj_id: = substr (Regexp_substr (r.t, ' OBJD: [[:d igit:]]+ '), 7);
Select object_name to V_obj_name from All_objects
where data_object_id = v_obj_id;
dbms_output.put_line (rpad (' object_id = ', ') | | v_obj_id);
dbms_output.put_line (rpad (' object_name = ', ') | | v_obj_name);
End If;

end Loop;

dbms_output.put_line ('------------------------------------------------');

end;
/

@which_obj2 6 2641
Old 1:alter System dump datafile &__file block &__block
New 1:alter System dump datafile 6 block 2641

System altered.

elapsed:00:00:00.01
------------------------------------------------
dba = 6/2641
type = First level BITMAP BLOCK
object_id = 9005
elapsed:00:00:00.045
object_name = t1_n1
------------------------------------------------
PL/SQL procedure successfully completed.