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

-- ROWID_INFO breaks ROWID into its components and returns them:
--
-- rowid_in - ROWID to be interpreted
-- rowid_type - type (restricted/extended)
-- object_number - data object number (rowid_object_undefined for restricted)
-- relative_fno - relative file number
-- block_number - block number in this file
-- file_number - file number in this block
-- ts_type_in - type of tablespace which this row belongs to
-- 'BIGFILE' indicates Bigfile Tablespace
-- 'SMALLFILE' indicates Smallfile (traditional pre-10i) TS.
-- NOTE: These two are the only allowed values for this param
--
procedure rowid_info( rowid_in IN rowid,
rowid_type OUT number,
object_number OUT number,
relative_fno OUT number,
block_number OUT number,
row_number OUT number,
ts_type_in IN varchar2 default 'SMALLFILE');[@more@]

创建相关函数，修改自eygle

create or replace package mytools is

-- Author : ADMINISTRATOR
-- Created : 2007-8-15 10:27:54
-- Purpose :

function get_rowid
(l_rowid in varchar2)
return varchar2;

end mytools;
/
create or replace package body mytools is

function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid varchar2(500);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
------------------
var_owner varchar2(30);
var_object_name varchar2(30);
var_object_type varchar2(19);

begin
dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);
------------------
select t.owner,t.object_name,t.object_type into var_owner, var_object_name, var_object_type
from all_objects t where t.object_id= object_number;

ls_my_rowid := 'Owner# is :'||to_char(var_owner)||chr(10)||
'Object_name is :'||to_char(var_object_name)||chr(10)||
'Object_type is :'||to_char(var_object_type)||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 get_rowid;

end mytools;
/

应用

SQL> SELECT A.ROWID FROM WQ.T1 A where ROWNUM<5;
AAADHoAAHAAABbSAAA
AAADHoAAHAAABbSAAB
AAADHoAAHAAABbSAAC
AAADHoAAHAAABbSAAD

SQL> select mytools.get_rowid('AAADHoAAHAAABbSAAA') row_id from dual;
Owner# is :WQ
Object_name is :T1
Object_type is :TABLE
Relative_fno is :7
Block number is :5842
Row number is :0

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

eygle

http://soft.zdnet.com.cn/software_zone/2007/0811/452344.shtml

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/79291/viewspace-957460/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/79291/viewspace-957460/