"Oracle" ROWID conversion RDBA,RDBA to file#block#

 ************************************************************************* * * * Original:blog.csdn.net/clark_xu Xu Changliang's Column************************************************************************  --Conversion of 1 rowID and RDBA

--storage properties of data rowID
--Data File file#
--Database Object obj#
--Data block number: block_no#
--Table line number
--Physical rowID and logic rowID
--Index using logical ROWID
--Other types using physical rowid
--ROWID Pseudo-Column
Select Rowid,id,time from Rangetable;
--Because the ROWID uniquely identifies a record, the index stores the ROWID value; Through the index, find the ROWID;
--The new base64 code. 32bit obj#,10bit rfile#,22bit block#,16bit row#
--so Maximum 1022 files per table space, maximum 4m=4000 blocks per data file
--The maximum number of rows in each block is 65536;
--Convert ROWID information by Dbms_rowid
Create or Replace function Get_rowid (L_rowid in VARCHAR2)
return VARCHAR2
Is
Ls_my_rowid VARCHAR2 (200);
Rowid_type number;
Object_number number;
RELATIVE_FNO number;
Block_number number;
Row_number number;
Begin
Dbms_rowid.rowid_info (L_rowid,rowid_type,object_number,relative_fno,block_number,row_number);

Ls_my_rowid:= ' object# is: '
|| To_char (object_number) | | Chr (10) | |
' Relative_fno is: '
|| To_char (RELATIVE_FNO) | | Chr (10) | |
' Block_number is: '
|| To_char (block_number) | | Chr (10) | |
' Row number is: '
|| To_char (row_number) | | Chr (10);

return ls_my_rowid;
End

Select Rowid,a.* from Dept A;
Select Get_rowid (' aaamfnaaeaaaaaqaaa ') row_id from dual;

2 RDBA conversion to file#,block#

--Dump the table space of the system
Alter session SET Events ' immediate trace name File_hdrs level 10 '
--View UDUMMP file DBA address
Wrote a simple function to convert file# and block# out of RDBA:

CREATE OR REPLACE FUNCTION getbfno (p_dba in VARCHAR2)
RETURN VARCHAR2
Is
L_str VARCHAR2 (255) DEFAULT NULL;
L_fno VARCHAR2 (15);
L_bno VARCHAR2 (15);
BEGIN
L_FNO: =
Dbms_utility.data_block_address_file (To_number (LTRIM (p_dba, ' 0x '), ' xxxxxxxx '));
L_bno: =
Dbms_utility.data_block_address_block (To_number (LTRIM (p_dba, ' 0x '), ' xxxxxxxx '));
L_STR: =
' datafile# is: '
|| L_fno
|| CHR (10)
|| ' DataBlock is: '
|| L_bno
|| CHR (10)
|| ' Dump Command:alter system dump datafile '
|| L_fno
|| ' Block '
|| L_bno
|| ‘;‘;
RETURN L_str;
END;
Select Getbfno (' 0x00400179 ') bfno from dual;

************************************************************************* * * * Original:blog.csdn.net/clark_xu Xu Changliang's Column************************************************************************  

"Oracle" ROWID conversion RDBA,RDBA to file#block#