--------------------------------------------------------------------------------
-- ~~~ xtender: based on: 
-- File name:   dba.sql
-- Purpose:     Convert Data Block Address (a 6 byte hex number) to file#, block#
--              and find to which segment it belongs
--
-- Author:      Tanel Poder
-- Copyright:   (c) http://www.tanelpoder.com
--              
-- Usage:       @dba <data_block_address>
--              @dba 40EB02
-- 	        
--	        
-- Other:       This script also tries to identify the segment into which this
--              block belongs. It first queries X$BH, as if this block is really
--              hot, it should be in buffer cache. Note that you can change this
--              query to use V$BH if you dont have access to X$ tables.
--              If the block is not in buffer cache anymore, then this script
--              can query DBA_EXTENTS next, but this can be a IO intensive operation
--              on some systems, so if X$BH already answers your question, press
--              CTRL+C here.
--
--------------------------------------------------------------------------------

col rfile# new_value v_dba_rfile
col block# new_value v_dba_block
col dba_object  head object for a40 truncate;
col dba_DBA     head DBA    for a20;
col dump_cmd                for a50;

select 
    dbms_utility.data_block_address_file(to_number('&1','XXXXXXXXXX')) RFILE#, 
    dbms_utility.data_block_address_block(to_number('&1','XXXXXXXXXX')) BLOCK#,
  '-- alter system dump datafile '||dbms_utility.data_block_address_file(to_number('&1','XXXXXXXXXX'))
  ||' block '||dbms_utility.data_block_address_block(to_number('&1','XXXXXXXXXX')) dump_cmd
from dual;

col dba_object  clear;
col dba_DBA     clear;
col dump_cmd    clear;

prompt #########################################################################################
prompt &_C_RED Enter 'Y' to find the segment using V$BH (this may take CPU time), &_C_RESET;
accept _chk prompt "'N' to cancel[N]: " default "N"

col STATE       for a20;
col BLOCK_CLASS for a30;
col OBJECT_TYPE for a30;
col object      for a30;

select  /*+ ORDERED */
        decode(bh.state,0,'free',1,'xcur',2,'scur',3,'cr',
4,'read',5,'mrec',
             6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',
             11,'donated', 12,'protected',13,'securefile', 14,'siop',15,'recckpt'
        ) state,
        decode(bh.class,1,'data block',2,'sort block',3,'save undo block', 
               4,'segment header',5,'save undo header',6,'free list',7,'extent map', 
               8,'1st level bmb',9,'2nd level bmb',10,'3rd level bmb', 11,'bitmap block',
               12,'bitmap index block',13,'file header block',14,'unused', 
               15,'system undo header',16,'system undo block', 17,'undo header',
               18,'undo block'
        ) block_class,
    o.object_type,
    o.owner||'.'||o.object_name		dba_object,
    bh.tch,
    bh.mode_held,
    decode(bitand(bh.flag,1),0, 'N', 'Y')       dirty,
    decode(bitand(bh.flag,16), 0, 'N', 'Y')     temp,
    decode(bitand(bh.flag,1536), 0, 'N', 'Y')   ping,
    decode(bitand(bh.flag,16384), 0, 'N', 'Y')  stale,
    decode(bitand(bh.flag,65536), 0, 'N', 'Y')  direct, 
    trim(to_char(bh.flag, 'XXXXXXXX'))||':'||
    trim(to_char(bh.lru_flag, 'XXXXXXXX'))      flg_lruflg,
    bh.dirty_queue                              DQ
from
    vx$bh         bh,
    dba_objects   o
where lower('&_chk')='y'
and bh.obj = o.data_object_id
and file#  = &v_dba_rfile
and dbablk = &v_dba_block
order by
    tch asc
/
col STATE       clear;
col BLOCK_CLASS clear;
col OBJECT_TYPE clear;
col object      clear;

prompt &_C_RED Enter 'Y' to query what segment resides there using DBA_EXTENTS (this can be IO intensive),&_C_RESET;
accept _chk prompt "'N' to cancel[N]: " default "N"
col     OWNER           format a30;
col     SEGMENT_NAME    format a30;
col     PARTITION_NAME  format a30;
col     TABLESPACE_NAME format a30;

select owner, segment_name, partition_name, tablespace_name 
from dba_extents
where lower('&_chk')='y'
and relative_fno = &v_dba_rfile
and &v_dba_block between block_id and block_id + blocks - 1
/
col     OWNER           clear;
col     SEGMENT_NAME    clear;
col     PARTITION_NAME  clear;
col     TABLESPACE_NAME clear;