-- getIdxRtBlk
-- 
-- File        : getIdxRtBlk.sql - Gather index related stats; blevel
-- Author      : Craig Shallahamer, craig@orapub.com
-- Date        : 4-Nov-2011 in Perth, Australia
-- Warrenty    : There is no warrenty, that is, use at your own risk. 
-- Purpose     : To gather the expected and actual index root block
--               datablock address (DBA).
--               
-- Usage       : SQL> start doRbExpr.sql <owner> <name> <trace file full path>

def owner=&1
def objNm=&2
def tracePath=&3

-- Make sure this is OK..
--
!rm /home/oracle/admin/diag/rdbms/prod18/prod18/trace/prod18_ora*trc
!rm /home/oracle/admin/diag/rdbms/prod18/prod18/trace/prod18_ora*trm

set serveroutput on
set echo off 
set feedback off

!echo "     ---  Generating (gathering) index stats..."
exec DBMS_STATS.GATHER_INDEX_STATS ('&owner','&objNm');

!echo "     ---  Getting index stats from data dictionary and by dumping index..."

declare
  hdrFlNo   number;
  idxHdrBlkNo number;
  idxRtDBA    number;
  theCmd    varchar2(200);
  objectId  number;
  bl          number;
begin
  select blevel         into bl       from dba_indexes where index_name = upper('&objNm');
  select header_file    into hdrFlNo  from dba_segments where segment_name = upper('&objNm');
  select header_block+1 into idxHdrBlkNo from dba_segments where segment_name = upper('&objNm');
  select dbms_utility.make_data_block_address(hdrFlNo,idxHdrBlkNo) into idxRtDBA from dual;

  select object_id into objectId from dba_objects where object_name = upper('&objNm');
  theCmd := 'alter session set events ''immediate trace name treedump level ' || objectId || '''';
  dbms_output.put_line('.    From data dictionary, DBA for header block plus one is: '|| idxRtDBA || ' blevel=' || bl);
  execute immediate theCmd;
end;
/
!trcRtDBA=`cat /home/oracle/admin/diag/rdbms/prod18/prod18/trace/prod18_ora*trc | grep branch | head -1 | awk '{print $3 " blevel=" $8}'`; echo "     From trace file after index dump, DBA is              : $trcRtDBA"