FreeLists / oracle-l / Re: Block address for a row in IOT table

["Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>]

I think this works on IOTs.  Not yet tested on 
partitioned IOTs.

select
  /*+ first_rows_1 */
  sys_op_lbid({object_id},'L',t.rowid),
  n, area, d
from
  PT_IOT  t
where
  "N" is not null
or "AREA" is not null
or "D" is not null
;

(n, area, d) is the primary key on my IOT, and the
ORs of 'is not null' are redundant in this case, but
relevant to an index with nullable columns.

The sys_op_lbid function is undocumented, and returns
the a rowid-like value which represents the rowid for 
the index entry that is the address of the FIRST entry
of the rowindex in the block that the index entry is in.
(So every return will end in AAA - for rowindex entry 0).
You can use the dbms_rowid functions to extract the file
id and block id from the rowid.

({object_id} is the INDEX object_id from user_objects) 


Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 10th Jan 2006

----- Original Message ----- 
From: "Alex Gorbachev" <gorbyx@xxxxxxxxx>
To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, February 01, 2006 7:53 AM
Subject: Block address for a row in IOT table


Hi all,

Does any one knows a simple way to figure our DBA for a row in an IOT
table? I can manually traverse the tree dumping blocks but that's a
bit time consuming if you need to do it for a number of rows.

Thanks in advance for any ideas,
Alex
--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l