[Oracle] converts rowid to rdba, and rdba to file # block #,

[Oracle] converts rowid to rdba, and rdba to file # block #,
 ************************************** *********************************** *** Original article: blog.csdn.net/clark_xu Xu changliang's column**************************************** ******************************** -- 1 rowid and rdba Conversion

-- Rowid of the data storage attribute
-- Data file #
-- Database object obj #
-- Data block number: block_no #
-- Table row number
-- Physical rowid and logical rowid
-- The Index uses the logical rowid
-- Physical rowid is used for other types
-- Rowid pseudo Column
Select rowid, id, time from rangetable;
-- Because rowid uniquely identifies a record, the index stores the rowid value. The rowid is found in the index;
-- New base64 encoding. 32bit obj #, 10bit rfile #, 22bit block #, 16bit row #
-- Therefore, each tablespace has a maximum of 1022 files, and each data file has a maximum of 4 MB = 4000 blocks.
-- The maximum number of rows in each block is 65536;
-- Use dbms_rowid to convert rowid Information
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;
Select get_rowid ('aaamfnaaeaaaaaqaaa ') row_id from dual;

2. Convert RDBA to file #, block #

-- Dump the tablespace of the system
Alter session set events 'immediate trace name FILE_HDRS level 10'
-- View the dba address of the udummp File
A simple function is written to convert file # And block # From RDBA:

Create or replace function getbfno (p_dba IN VARCHAR2)
RETURN VARCHAR2
IS
Rochelle STR VARCHAR2 (255) default null;
Rochelle fno VARCHAR2 (15 );
Rochelle BNO VARCHAR2 (15 );
BEGIN
Rochelle fno: =
DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x '), 'xxxxxxxx '));
Rochelle BNO: =
DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x '), 'xxxxxxxx '));
Rochelle 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 article: blog.csdn.net/clark_xu Xu changliang's column**************************************** ********************************