All about rowid in Oracle Database 11g
All about rowid in Oracle Database 11g
dbms_rowidrowidrowid_block_numberROWID_RELATIVE_FNOOracle database SQL> select t.rowid,t.* from scott.test t; ROWID DEPTNO DNAME LOC ------------------ ---------- -------------- ------------- AAAPtpAAEAAAADbAAA 10 ACCOUNTING NEW YORK AAAPtpAAEAAAADbAAB 20 RESEARCH DALLAS AAAPtpAAEAAAADbAAC 30 SALES CHICAGO AAAPtpAAEAAAADbAAD 40 OPERATIONS BOSTON rowid(AAAPtpAAEAAAADbAAD)详解 AAAPtp --object# AAE --file# AAAADb --block# AAD --row# SQL> select rowid , 2 substr(rowid,1,6) "OBJECT#", 3 substr(rowid,7,3) "FILE#", 4 substr(rowid,10,6) "BLOCK#", 5 substr(rowid,16,3) "ROW#" 6 from scott.test; ROWID OBJECT# FILE# BLOCK# ROW# ------------------ ------------------------ ------------ ------------------------ ------------ AAAPtpAAEAAAADbAAA AAAPtp AAE AAAADb AAA AAAPtpAAEAAAADbAAB AAAPtp AAE AAAADb AAB AAAPtpAAEAAAADbAAC AAAPtp AAE AAAADb AAC AAAPtpAAEAAAADbAAD AAAPtp AAE AAAADb AAD 转换 rowid 的 object# SQL> select DBMS_ROWID.ROWID_OBJECT('AAAPtpAAEAAAADbAAD') from dual 2 union all 3 select object_id from dba_objects where object_name = 'TEST' AND owner='SCOTT'; DBMS_ROWID.ROWID_OBJECT('AAAPTPAAEAAAADBAAD') --------------------------------------------- 64361 64361 转换 rowid 的 file# (相对文件编号) SQL> select DBMS_ROWID.ROWID_RELATIVE_FNO('AAAPtpAAEAAAADbAAD') from dual 2 union all 3 select a.file_id from dba_data_files a,dba_segments b where b.SEGMENT_NAME ='TEST' and b.owner='SCOTT' and a.tablespace_name=b.tablespace_name; DBMS_ROWID.ROWID_RELATIVE_FNO('AAAPTPAAEAAAADBAAD') --------------------------------------------------- 4 4 转换 rowid 的 block# SQL> select dbms_rowid.rowid_block_number('AAAPtpAAEAAAADbAAD') from dual; DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAPTPAAEAAAADBAAD') --------------------------------------------------- 219 转换 rowid 的 row# SQL> select DBMS_ROWID.ROWID_ROW_NUMBER(rowid) from scott.test; DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) ---------------------------------- 0 1 2 3 转换 rowid 的绝对文件编号 SQL> select DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO('AAAPtpAAEAAAADbAAD','SCOTT','TEST') from scott.test; DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO('AAAPTPAAEAAAADBAAD','SCOTT','TEST') --------------------------------------------------------------------- 4 4 4 4