Shulou(Shulou.com)06/01 Report--

How to dump-oracle the block of index

1, create a new table test

SYS@127.0.0.1:1521/oracle12cpdb > create table test as select object_id from dba_objects

Table created.

2. After you create a new table, create a new index on the column of the table

SYS@127.0.0.1:1521/oracle12cpdb > create index ind_test on test (object_id)

Index created.

Refresh buffer_cache back to disk

SYS@127.0.0.1:1521/oracle12cpdb > alter system flush buffer_cache

System altered.

3. Get the object_id of index by querying the dba_objects table

SYS@127.0.0.1:1521/oracle12cpdb > select object_id from dba_objects where OBJECT_NAME='IND_TEST' and OBJECT_TYPE='INDEX'

OBJECT_ID 73204

4. Dump the index tree

SYS@127.0.0.1:1521/oracle12cpdb > alter system set events' immediate trace name treedump level 73204'

System altered.

5. Query the trace file of the process and get the following information

-begin tree dump

Branch: 0x407a51 4225617 (0: nrow: 161, level: 1)

Leaf: 0x407a52 4225618 (- 1: row:485.485 avs:823)

Leaf: 0x407a53 4225619 (0: row:479.479 avs:816)

Leaf: 0x407a54 4225620 (1: row:479.479 avs:816)

Leaf: 0x407a55 4225621 (2: row:479.479 avs:816)

Leaf: 0x407a56 4225622 (3: row:479.479 avs:816)

Leaf: 0x407a57 4225623 (4: row:478.478 avs:830)

.

-end tree dump

6. Use dbms_utility to find out the file and block numer of index

SYS@127.0.0.1:1521/oracle12cpdb > select dbms_utility.data_block_address_file (4225617), dbms_utility.data_block_address_block (4225617) from dual

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (4225617) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (4225617) 1 31313

7. According to the above query, index has a block of 31313 in file 1. Dump this block.

SYS@127.0.0.1:1521/oracle12cpdb > alter system dump datafile 1 block 31313

System altered.

Generate the dump file, through which you can see how index key and rowid are stored on block.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.