Re: rowid in blockid

"silver" <franz_at_franz-rustler.com> wrote in message news:<3d80b6a3$1_2_at_news.premium-news.net>...
> hello
>
> how can i find out the rowids for the given blockids.
>
> thanks franz

Not sure I really understand the question, but here goes anyway:

ROWIDs are not stored anywhere for rows in a table, but are computed 'on-the-fly' as needed. As a result, they are sometimes referred to as a 'pseudocolumn' -you can treat them as though they physically existed, but they don't in reality.

Furthermore, a ROWID is simply a concatenation of four other pieces of information: the object ID (ie, the unique ID associated with each segment within the database), the File Number, the Block Number and the row number within the block.

Therefore, since you presumably already know which block you are talking about (you say you are talking about "given blockids"), and must therefore also presumably know what data file you are dealing with, you are practically already there. All you need to know to fill in the gap is the object ID -and you can work that out by querying dba_extents like this:

select o.object_id, e.owner, e.segment_name from dba_objects o, dba_extents e
where e.file_id=<your file number>
and <your block number> between e.block_id and (e.block_id + e.blocks+1)
and o.owner=e.owner
and o.object_name=e.segment_name;

With the object number, file number and block number to hand, you now have all you need to know to work out what ROWIDs are within your given block. You may need to look at the DBMS_ROWID package to convert your decimal information into the Base-64 encoding that true ROWIDs use.

For example,

select dbms_rowid.rowid_create(1,5334,1,30663,1) from dual;

...gives you the base-64 encoding in the extended rowid form for object 5334, in file 1, with block number 30663 and for the first row in that block.

Regards
HJR Received on Thu Sep 12 2002 - 17:58:03 CDT