巧用dbms_rowid包获得rowid的详细信息

 

转载于：http://database.51cto.com/art/200805/73789.htm

• 摘要：我们知道，Rowid中包含了记录的详细信息，通过dbms_rowid包可以很轻松地获得这些信息.本文将通过一个自定义函数详细介绍这个package的使用。

•  

  我们知道，Rowid中包含了记录的详细信息，通过dbms_rowid包可以很轻松地获得这些信息.本文将通过一个自定义函数详细介绍这个package的使用。

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); return ls_my_rowid ; end; /

其它的用法:

[oracle@jumper tools]$ sqlplus scott/tiger SQL*Plus: Release 9.2.0.4.0 - Production on Sun Nov 7 12:30:19 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning option JServer Release 9.2.0.4.0 - Production SQL> set echo on SQL> @f_get_rowid SQL> create or replace function get_rowid 2 (l_rowid in varchar2) 3 return varchar2 4 is 5 ls_my_rowid varchar2(200); 6 rowid_type number; 7 object_number number; 8 relative_fno number; 9 block_number number; 10 row_number number; 11 begin 12 dbms_rowid.rowid_info(l_rowid,rowid_type, object_number,relative_fno, block_number, row_number); 13 ls_my_rowid := 'Object# is :'||to_char(object_number)||chr(10)|| 14 'Relative_fno is :'||to_char(relative_fno)||chr(10)|| 15 'Block number is :'||to_char(block_number)||chr(10)|| 16 'Row number is :'||to_char(row_number); 17 return ls_my_rowid ; 18 end; 19 / Function created. SQL> SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select rowid,a.* from dept a; ROWID DEPTNO DNAME LOC ------------------ ---------- -------------- ------------- AAABiPAABAAAFRSAAA 10 ACCOUNTING NEW YORK AAABiPAABAAAFRSAAB 20 RESEARCH DALLAS AAABiPAABAAAFRSAAC 30 SALES CHICAGO AAABiPAABAAAFRSAAD 40 OPERATIONS BOSTON SQL> col row_id for a60 SQL> select get_rowid('AAABiPAABAAAFRSAAA') row_id from dual; ROW_ID ------------------------------------------------------------ Object# is :6287 Relative_fno is :1 Block number is :21586 Row number is :0 SQL> select get_rowid('AAABiPAABAAAFRSAAB') row_id from dual; ROW_ID ------------------------------------------------------------ Object# is :6287 Relative_fno is :1 Block number is :21586 Row number is :1 SQL>