Re: Block address for a row in IOT table

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_at_gmail.com> To: "ORACLE-L" <oracle-l_at_freelists.org> 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