TFTS: Converting DBA's (Database Addresses) to File # and Block #_113005.1

rongshiyuan發表於2014-04-04



9876
Click to add to Favorites TFTS: Converting DBA's (Database Addresses) to File # and Block # (Doc ID 113005.1) To BottomTo Bottom

Modified:Mar 2, 2013Type:BULLETIN
Rate this document Email link to this document Open document in new window Printable Page

 =======
PURPOSE
=======

On occasion it becomes necessary to convert a DBA (Database Address) to a file
number and block number.  The need most often arises from block corruption  

 
=========== 
Disclaimer: 
=========== 
 
This script is provided for educational purposes only. It is NOT supported by 
Oracle Support Services.  The script has been tested and appears to work as 
intended.  However, you should always test any script before relying on it. 
 
PROOFREAD THIS SCRIPT BEFORE USING IT!  Due to differences in the way text  
editors, email packages and operating systems handle text formatting  
(i.e. spaces, tabs and carriage returns), this script may not be in an 
executable state when you first receive it.  Check over the script to ensure 
that errors of this type are corrected. 

============= 
Requirements: 
============= 
 
Should be run from any DBA account


================
INPUT PARAMETERS
================

EXEC CDBA('','') 


======= 
Script: 
======= 
 
----------- cut ---------------------- cut -------------- cut -------------- 

CREATE OR REPLACE PROCEDURE cdba ( iblock VARCHAR2, imode VARCHAR2 ) AS

x       NUMBER;
digits# NUMBER;
results NUMBER := 0;
file#   NUMBER := 0;
block#  NUMBER := 0;
cur_digit CHAR(1);
cur_digit# NUMBER;

BEGIN
      IF upper(imode) = 'H' THEN
           digits# := length( iblock );
           FOR x  IN 1..digits#  LOOP
                cur_digit := upper(substr( iblock, x, 1 ));
                IF cur_digit IN ('A','B','C','D','E','F')  THEN
                     cur_digit# := ascii( cur_digit ) - ascii('A') +10;
                ELSE
                     cur_digit# := to_number(cur_digit);
                END IF;
                results := (results *16) + cur_digit#;
           END LOOP;
      ELSE
           IF upper(imode) = 'D' THEN
                results := to_number(iblock);
           ELSE
                dbms_output.put_line('H = Hex Input ... D = Decimal Input');
                RETURN;
           END IF;
      END IF;

      file#  := dbms_utility.data_block_address_file(results);
      block# := dbms_utility.data_block_address_block(results);

      dbms_output.put_line('.');
      dbms_output.put_line( 'The file is ' || file# );
      dbms_output.put_line( 'The block is ' || block# );
END;
/


----------- cut ---------------------- cut -------------- cut --------------

=========
EXAMPLES:
=========

SQL> set serveroutput on

SQL> exec cdba('04c00f02','h');
.
The file is 19
The block is 3842

PL/SQL procedure successfully completed.

SQL> exec cdba(345618272,'D');
.
The file is 82
The block is 1685344

PL/SQL procedure successfully completed.

SQL> exec cdba('04c00f02','k');
H = Hex Input ... D = Decimal Input

NOTE: If you are converting a hexidecimal number labeled as "rdba:" in the tracefile or alert log, or output of an ORA-8103 error, be aware that this is the relative file number...
          
A relative file number is not the absolute file number  

The following query will display 
   rfile# (relative file number) 
 and 
   file# (absolute file number)

   If they are not the same ... use file# instead of rfile# when trying to determine the object.

SELECT rfile#, file# FROM v$datafile WHERE rfile#=;




Additional Search Words
-----------------------

uba, rba, dba, odba, hex, decimal, convert, O8, 8i 

 

Related

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17252115/viewspace-1136226/,如需轉載,請註明出處,否則將追究法律責任。

相關文章