"Oracle" ROWID interpretation

The ROWID records the physical location of the data, and through ROWID you can know that the data is stored in the first few blocks of the data file.

---------------------extended rowid format ---------------------------------------------------- aaaeof           aae              AAAACL     AAA----------------------------------------------------Data object   relative file       block     row number         number              Number    number----------------------The first part: Data segment Id select * from dba_ objects  Part II: Relative numbering of files  dba_data_files Part III: block numbering Part IV: line numbering, the first few lines on this block ROWID: Record the physical location of this data rowid using 64 binary, The definition is as follows:-----------------------------a~z : 0~25a~z : 26~510~9 : 52-61+    : 62    /   : 63------------------------------- 

Example: AAE to Decimal, the method is as follows: A = 0E = 4AAE = 0*64^2 + 0*64^1 + 4*64^0 = 4 Conversion process: The AAE analysis into 64 binary data, and then each single digit *64 the N-square, and then add the value is 10 binary.

The conversion principle is clear and the following code is used to parse the ROWID information, but the code is written too ...

----------------------------------------------------------Aaay50 aag   aaaacl aaa--1, 6    7,3   10,6  16,3--processing rowidcreate or replace  Procedure v_oprowid (V_ROWID&NBSP;IN&NBSP;VARCHAR2) asv_dataid varchar2 (6); v_rfileid varchar2 (3); V_BLOCKID&NBSP;VARCHAR2 (6); v_xingid varchar2 (3); V_all varchar (+);v_chk number;v_temp  Number;v_temp64 number default 0;begin  select substr (v_rowid,1,6), substr (v_ rowid,7,3), substr (v_rowid,10,6),   substr (v_rowid,16,3)  into v_dataid,v_rfileid,v_blockid,v _xingid from dual;  --dbms_output.put_line (' objecct_id: ' | | v_dataid| | ', Rfileid: '   --| | v_rfileid| | ', Blockid: ' | | v_blockid| | ', Xingid: ' | | V_xingid);   for i in 1..6 loop    v_temp:=ascii (substr (v_ dataid,i,1)); &NBSP;&NBSP;&NBSP;&NBSP;V_CHK:=&NBSP;V_TEMP-65;&NBSP;&NBSP;&NBSP;&NBsp;if v_chk>=0 and v_chk <= 25 then         v_temp64 :=v_temp64+v_chk*power (64,6-i);    end if;     if v_chk < 0 and v_chk>=-17 then         v_temp64 :=v_temp64+ (v_chk+69) *power (64,6-i);    end if;         if v_chk >=32 and v_chk<=57 then          v_temp64 :=v_temp64+ (v_chk-6) *power (64,6-i);     end if;    if v_chk=-22 then          v_temp64 :=v_temp64+62*power (64,6-i);    end if;          if v_chk=-18 then          v_temp64 :=v_temp64+63*power (64,6-i);    end if;       End loop;  dbms_output.put_line (' object_data_id: ' | | V_TEMP64);    --relative_fno      v_temp64:=0;    for i in 1..3 loop    v_temp:=ascii (substr (v_rfileid,i,1));     v_chk:= v_temp-65;    if v_chk>=0 and v_chk  <= 25 then        v_temp64 :=v_temp64+v_chk* Power (64,3-i);    end if;    if v_chk < 0  and v_chk>=-17 then        v_temp64 :=v_temp64+ (v_ chk+69) *power (64,3-i);     end if;        if  v_chk >=32 and v_chk<=57 then         v_temp64 :=v_temp64+ (v_chk-6) *power (64,3-i);     end if;    if v_chk=-22 then          v_temp64 :=v_temp64+62*power (64,3-i);    end if;          if v_chk=-18 then          v_temp64 :=v_temp64+63*power (64,3-i);    end if;       end loop;  dbms_output.put_line (' relative_fno: ' | | V_TEMP64);   --  v_temp64:=0;  for i in 1..6 loop     v_temp:=ascii (substr (v_blockid,i,1));    v_chk:= v_temp-65;     if v_chk>=0 and v_chk <= 25 then         v_temp64 :=v_temP64+v_chk*power (64,6-i);    end if;    if v_chk <  0 and v_chk>=-17 then        v_temp64 :=v _temp64+ (v_chk+69) *power (64,6-i);    end if;         if v_chk >=32 and v_chk<=57 then          v_temp64 :=v_temp64+ (v_chk-6) *power (64,6-i);    end if;     if v_chk=-22 then         v_ Temp64 :=v_temp64+62*power (64,6-i);    end if;          if v_chk=-18 then         v_ Temp64 :=v_temp64+63*power (64,6-i);    end if;       end loop;  dbms_oUtput.put_line (' blockid: ' | | V_TEMP64);  ---  v_temp64:=0;   for i in 1..3 loop     v_temp:=ascii (substr (v_xingid,i,1));    v_chk:= v_temp-65;     if v_chk>=0 and v_chk <= 25 then         v_temp64 :=v_temp64+v_chk*power (64,3-i);     end if ;    if v_chk < 0 and v_chk>=-17 then         v_temp64 :=v_temp64+ (v_chk+69) *power (64,3-i);     end if;        if v_chk >=32 and v_chk< =57 then         v_temp64 :=v_temp64+ (v_chk-6) *power ( 64,3-I);     end if;    if v_chk=-22 then  &Nbsp;      v_temp64 :=v_temp64+62*power (64,3-i);     end  if;         if v_chk=-18 then          v_temp64 :=v_temp64+63*power (64,3-i);     end  if;      end loop;  dbms_output.put_line (' Xingid: ' | | V_TEMP64);   end;--procedure  End----------------------------------------------------------------

Execution method:

Sql> set Serveroutput onsql> exec v_oprowid (' Aaay50aagaaaaclaab ') object_data_id:102004relative_fno:6blockid : The 139xingid:1pl/sql process has completed successfully. Sql>

Oracle provides functions in the DBMS_ROWID package that can read rowID to parse into readable information, with the following statements:

Select Dbms_rowid.rowid_object (ROWID), Dbms_rowid.rowid_relative_fno (ROWID), Dbms_rowid.rowid_block_number (ROWID) , Dbms_rowid.rowid_row_number (ROWID), rowid from wen.t;

This article from "Despite the wrong, let me wrong to die!" "Blog, be sure to keep this provenance http://hxw168.blog.51cto.com/8718136/1556624

"Oracle" ROWID interpretation