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

Last Updated: Sunday, 2004-11-07 12:46 Eygle

Rowid中包含了记录的详细信息，通过dbms_rowid包可以获得这些信息.本文通过一个定义自定义函数介绍该package的使用.

create or replace function get_rowid

(l_rowid in varchar2)

return varchar2

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

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>

本文作者:

eygle,Oracle技术关注者,来自中国最大的Oracle技术论坛itpub.

www.eygle.com是作者的个人站点.你可通过Guoqiang.Gai@gmail.com来联系作者.欢迎技术探讨交流以及链接交换.

原文出处:

http://www.eygle.com/faq/Use.dbms_rowid.Package.Get.Detail.Of.Rowid.htm