Mapping between resources and datablocks by using ROWID

EMP table:
SQL> select owner, data_object_id, object_name from dba_objects where owner='SCOTT' and object_name='EMP';
  OWNER                          DATA_OBJECT_ID OBJECT_NAM
  ------------------------------ -------------- ----------
  SCOTT                                   75335 EMP
SQL> select rowid, empno from scott.emp where empno in ( 7788, 7876);
  ROWID               EMPNO
  ------------------ ----------
  AAASZHAAEAAAACXAAH     7788
  AAASZHAAEAAAACXAAK     7876
SQL> select dbms_rowid.rowid_block_number('AAASZHAAEAAAACXAAH') Block_No, dbms_rowid.rowid_relative_fno('AAASZHAAEAAAACXAAH')  File_No  from dual;
    BLOCK_NO    FILE_NO
  ---------- ----------
         151        4


SQL> select b.kjblname hexname, b.kjblname2 resource_name, b.kjblgrant, b.kjblrole, b.kjblrequest
  from X$LE a , X$KJBL b where
 a.le_kjbl=b.kjbllockp  and a.le_addr = 
  ( select le_addr from x$bh where obj=75335 and dbablk=151 and and state !=3); 
  HEXNAME                        RESOURCE_NAME                   KJBLGRANT   KJBLROLE KJBLREQUE
  ------------------------------ ------------------------------ --------- ---------- ---------
  [0x97][0x4],[BL][ext 0x0,0x0]  151,4,BL                        KJUSEREX         0 KJUSERNL
Translation_ 
   [0x97] = 151 = BLOCK_NO ( see above ROWID query )
   [0x4]  =   4 = FILE_NO  ( see above ROWID query ) 

Dump the related buffer cache block
SQL> alter system dump datafile 4 block 151;

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>