Home » RDBMS Server » Server Administration » how to get block_number of index (11.2.0.1 windows xp)
how to get block_number of index [message #538093] Wed, 04 January 2012 00:11 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
If i know a rowid of a row,how to get the block_number of index?


SQL> select rowid from hxl.tb_test where id=1;

ROWID
------------------
AAAMo/AAFAAAAAOAAA
Re: how to get block_number of index [message #538098 is a reply to message #538093] Wed, 04 January 2012 00:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>If i know a rowid of a row,how to get the block_number of index?

does rowid only exist in index?

can rowid exist in table?
Re: how to get block_number of index [message #538101 is a reply to message #538098] Wed, 04 January 2012 00:48 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks BlackSwan,
I can use the rowid_block_number tp get the block number of the row in table tb_test,but i can not get the block number of the row in index idx_tb_test_id.
select dbms_rowid.rowid_block_number(rowid) from hxl.tb_test where id=1;

[Updated on: Wed, 04 January 2012 00:48]

Report message to a moderator

Re: how to get block_number of index [message #538102 is a reply to message #538093] Wed, 04 January 2012 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DBMS_ROWID package to get information from a rowid.

Quote:
how to get the block_number of index?


This is meaningless and irrelevant, or you have to better explain what you want.

Regards
Michel
Re: how to get block_number of index [message #538114 is a reply to message #538102] Wed, 04 January 2012 02:24 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks!
Re: how to get block_number of index [message #538257 is a reply to message #538114] Thu, 05 January 2012 01:01 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
Michel,just as the flowing,we can get the block number of a index.

SQL> select object_id,object_name from dba_objects where owner='HXL';

 OBJECT_ID OBJECT_NAME
---------- ---------------------------------------------
     51786 IDX_TB_TEST_N1


alter session set events 'immediate trace name treedump level 51786'


[oracle@hxl udump]$ more oracl_ora_2679.trc
/u01/app/oracle/admin/oracl/udump/oracl_ora_2679.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: hxl
Release: 2.6.9-42.0.0.0.1.ELhugemem
Version: #1 SMP Sun Oct 15 14:06:18 PDT 2006
Machine: i686
Instance name: oracl
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 2679, image: oracle@hxl (TNS V1-V3)

*** 2012-01-04 18:23:29.845
*** SERVICE NAME:(SYS$USERS) 2012-01-04 18:23:29.791
*** SESSION ID:(144.20) 2012-01-04 18:23:29.789
----- begin tree dump
branch: 0x1400494 20972692 (0: nrow: 2, level: 1)
leaf: 0x1400497 20972695 (-1: nrow: 540 rrow: 540)
leaf: 0x1400498 20972696 (0: nrow: 23 rrow: 23)
----- end tree dump


SELECT dbms_utility.data_block_address_file(to_number(ltrim('0x1400494',
                                                            '0x'),
                                                      'xxxxxxxx')) file#,
       dbms_utility.data_block_address_block(to_number(ltrim('0x1400494',
                                                             '0x'),
                                                       'xxxxxxxx')) block#
  7    FROM dual;

     FILE#     BLOCK#
---------- ----------
         5       1172


Now the 1172 is the block number of index IDX_TB_TEST_N1.
Re: how to get block_number of index [message #538261 is a reply to message #538257] Thu, 05 January 2012 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now the 1172 is the block number of index IDX_TB_TEST_N1.

This is meaningless, an index is not just a block.
In addition, if this is the firt block you want, you have this information in dba_extents, no need to dump.

In the end, and then?

Regards
Michel

[Edit: typo]

[Updated on: Thu, 05 January 2012 01:36]

Report message to a moderator

Re: how to get block_number of index [message #538267 is a reply to message #538261] Thu, 05 January 2012 01:33 Go to previous message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thank you,I know!
Previous Topic: how to read dump contents
Next Topic: what's the maximum size of the control file ?
Goto Forum:
  


Current Time: Thu Mar 28 13:15:47 CDT 2024