dump index and determin if it is a index root block

转载索引，判端是不是根块[@more@]

Once we get the object_id from dba_segments, here's how to dump an index:

1、dump index
alter session set events 'immediate trace name treedump level :ObjectId';

...
----- begin tree dump
branch: 0x3ec4d5f4 1053087220 (0: nrow: 5, level: 2)
branch: 0x58515106 1481724166 (-1: nrow: 491, level: 1)
leaf: 0x3ec4d5f5 1053087221 (-1: nrow: 46 rrow: 46)
leaf: 0x3ec4d5f6 1053087222 (0: nrow: 45 rrow: 45)
leaf: 0x3ec4d5f7 1053087223 (1: nrow: 44 rrow: 44)
leaf: 0x3ec4d5f8 1053087224 (2: nrow: 44 rrow: 44)
leaf: 0x58456ee1 1480945377 (3: nrow: 45 rrow: 45)
leaf: 0x58456ee2 1480945378 (4: nrow: 44 rrow: 44)
leaf: 0x58456ee3 1480945379 (5: nrow: 44 rrow: 44)
leaf: 0x58456ee4 1480945380 (6: nrow: 45 rrow: 45)
leaf: 0x58456ee5 1480945381 (7: nrow: 45 rrow: 45)
leaf: 0x58456ee6 1480945382 (8: nrow: 45 rrow: 45)
...

2、获取index根块的DBA
其中0x3ec4d5f4 就是根块DBA
SQL> SELECT HEADER_FILE, HEADER_BLOCK
2 FROM DBA_SEGMENTS
3 WHERE OWNER = 'UCR_CRM2'
4 AND SEGMENT_NAME = 'PK_TF_F_USER_PURCHASE';

HEADER_FILE HEADER_BLOCK
----------- ------------
251 316915
We must remember to add one to the header block number

SQL> select dbms_utility.make_data_block_address(251,316916) from dual;

DBMS_UTILITY.MAKE_DATA_BLOCK_A
------------------------------
1053087220

3、DBA转换为file_id和block_id
SQL> SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(1053087220) FILE_ID,
2 DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(1053087220) BLOCK_ID
3 FROM DUAL;

FILE_ID BLOCK_ID
---------- ----------
251 316916

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/717880/viewspace-1057606/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/717880/viewspace-1057606/