Re: Block address for a row in IOT table
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <gorbyx@xxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Feb 2006 10:47:57 -0000
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
