OraFAQ Forum: Server Administration » how to get block

Home » RDBMS Server » Server Administration » how to get block_number of index (11.2.0.1 windows xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to get block_number of index [message #538093]

Wed, 04 January 2012 00:11

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

Report message to a moderator

Re: how to get block_number of index [message #538098 is a reply to message #538093]

Wed, 04 January 2012 00:22

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?

Report message to a moderator

Re: how to get block_number of index [message #538101 is a reply to message #538098]

Wed, 04 January 2012 00:48

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: how to get block_number of index [message #538114 is a reply to message #538102]

Wed, 04 January 2012 02:24

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks!

Report message to a moderator

Re: how to get block_number of index [message #538257 is a reply to message #538114]

Thu, 05 January 2012 01:01

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.

Report message to a moderator

Re: how to get block_number of index [message #538261 is a reply to message #538257]

Thu, 05 January 2012 01:09

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thank you,I know!

Report message to a moderator

Previous Topic:	how to read dump contents
Next Topic:	what's the maximum size of the control file ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 27 06:23:21 CST 2025