Oracle dump datafile and IOT Secondary Index

在前一篇 IOT, Secondary Index and Mapping Table 中提到IOT的secondary index中应该包含了IOT的primary key， 但是不知道怎么去证明一下。网上看到可以通过如下方式dump数据文件中block的内容，

To dump single block use following command:
alter system dump datafile <file number> block <block number> ;

To dump multiple block use following command:
alter system dump datafile <file number> block min <first block number> block max <last block number> ;

于是想到是否可以搞搞看看。此文就对这个探索过程简单记录一下....

现在拿之前创建的IOT表TEST_IOT来试验一下，

SQL> desc TEST_IOT;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER
 NAME                                                           VARCHAR2(128)

SQL> select * from dba_extents where segment_name='TEST_IOT';

no rows selected

太奇怪了！！明明是存在TEST_IOT这张表的，但是居然从dba_extents中找不到，这不是有点扯dan吗？？？  难倒是Oracle错了？？ 我非常底气不足地想到这个可能性。:)

虽然知道这个可能性微乎其微i，但是问题到底出在什么地方呢？ 

Think...think....think...突然意识到TEST_IOT不是普通的heap表，而其实就是个索引，而索引的segment name就是索引的名字。那么TEST_IOT的索引是啥呢？自然应该是TEST_IOT的主键名, 来查查看...

SQL> select index_name, index_type from user_indexes where table_name='TEST_IOT';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
IDX_TEST_IOT_1                 NORMAL
SYS_IOT_TOP_262816             IOT - TOP

由于在创建TEST_IOT的时候没有显示指出primary key的名字，因此系统是自动生成，也就是SYS_IOT_TOP_262816. 

知道TEST_IOT对应的segment的名字，就可以通过查询dba_extents来获取TEST_IOT对应的数据文件和起始block id了， 

SQL> select file_id, block_id from dba_extents where segment_name='SYS_IOT_TOP_262816';

   FILE_ID   BLOCK_ID
---------- ----------
         4       9561

因为我是想看看TEST_IOT上的secondary index的，因此现在来分析下TEST_IOT上的二级索引 -- IDX_TEST_IOT_1, 

SQL> select file_id, block_id, blocks from dba_extents where owner='FRANK' and segment_name='IDX_TEST_IOT_1';

   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         4       9569          8

可以看到索引IDX_TEST_IOT_1位于datafile 4, 起始block id为9569， 占用了8个block. 那么把这8个block都dump出来看看....

SQL> alter system dump datafile 4 block min 9569 block max 9576;

System altered.

  那么生成的trace文件在哪里呢？网上普遍流行的一种方法是用下面的SQL来获得trace文件的名字，

SET LINESIZE 100
COLUMN trace_file FORMAT A60
SELECT s.sid,
       s.serial#,
       pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||    
       '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
       v$process p,
       v$parameter pa
WHERE  pa.name = 'user_dump_dest'
AND    s.paddr = p.addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

但是很奇怪的是，我用这个SQL得到的文件名在系统中根本就没有找到！！！ 上面这个SQL显然是从user_dump_dest里面找生成的trace文件，而且认为trace文件的命名方式是<instance_name>_ora_<process_id>, 就像如下所示，

       SID    SERIAL# TRACE_FILE
---------- ---------- ------------------------------------------------------------
       107      12595 E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP/orcl_ora_2832.trc

但是我却没有从udump文件夹下找到这个trace文件，压根就不存在！！后来我在bdump 文件夹下面发现了产生的trace文件，而且文件的名字叫 orcl_s002_2372.trc！我想这个S002应该表示SMON进程产生的trace文件吧。这个问题需要验证下！

OK， 回归到主题上来。从trace文件中摘取如下一段（index的leaf block)来分析IOT的secondary index的构成情况， 

Leaf block dump
===============
header address 161882724=0x9a62264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: opcode=0: iot flags=I-- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 32
kdxcofbo 100=0x64
kdxcofeo 6988=0x1b4c
kdxcoavs 6937
kdxlespl 0
kdxlende 1
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8003] flag: K-----, lock: 0, len=33
col 0; len 19; (19):  41 51 24 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45
col 1; len 2; (2):  c1 1a
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#1[7968] flag: K-----, lock: 0, len=35
col 0; len 21; (21): 
 41 51 24 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 52
col 1; len 2; (2):  c1 1d
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#2[7933] flag: K-----, lock: 0, len=35
col 0; len 21; (21): 
 41 51 24 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 53
col 1; len 2; (2):  c1 0d
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#3[7897] flag: K-----, lock: 0, len=36
col 0; len 22; (22): 
 41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 45
col 1; len 2; (2):  c1 18
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#4[7861] flag: K-----, lock: 0, len=36
col 0; len 22; (22): 
 41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 46
col 1; len 2; (2):  c1 19
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#5[7825] flag: K-----, lock: 0, len=36
col 0; len 22; (22): 
 41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 47
col 1; len 2; (2):  c1 14
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#6[7789] flag: K-----, lock: 0, len=36
col 0; len 22; (22): 
 41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 48
col 1; len 2; (2):  c1 11
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#7[7753] flag: K-----, lock: 0, len=36
col 0; len 22; (22): 
 41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 49
col 1; len 2; (2):  c1 16
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#8[7717] flag: K-----, lock: 0, len=36
col 0; len 22; (22): 
 41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 4e
col 1; len 2; (2):  c1 0e
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#9[7681] flag: K-----, lock: 0, len=36
col 0; len 22; (22): 
 41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 53
col 1; len 2; (2):  c1 09
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#10[7645] flag: K-----, lock: 0, len=36
col 0; len 22; (22): 
 41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 54
col 1; len 2; (2):  c1 0c
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#11[7609] flag: K-----, lock: 0, len=36
col 0; len 22; (22): 
 41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 56
col 1; len 2; (2):  c1 0b
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#12[7584] flag: K-----, lock: 0, len=25
col 0; len 11; (11):  4d 45 53 53 41 47 45 5f 41 50 49
col 1; len 2; (2):  c1 04
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#13[7559] flag: K-----, lock: 0, len=25
col 0; len 11; (11):  4d 45 53 53 41 47 45 5f 41 50 49
col 1; len 2; (2):  c1 05
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#14[7533] flag: K-----, lock: 0, len=26
col 0; len 12; (12):  53 59 53 5f 43 30 30 35 34 36 37 32
col 1; len 2; (2):  c1 06
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#15[7507] flag: K-----, lock: 0, len=26
col 0; len 12; (12):  53 59 53 5f 43 30 30 35 34 36 37 35
col 1; len 2; (2):  c1 0a
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#16[7474] flag: K-----, lock: 0, len=33
col 0; len 19; (19):  53 59 53 5f 49 4f 54 5f 4f 56 45 52 5f 32 36 32 34 37 32
col 1; len 2; (2):  c1 13
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#17[7442] flag: K-----, lock: 0, len=32
col 0; len 18; (18):  53 59 53 5f 49 4f 54 5f 54 4f 50 5f 32 36 32 34 36 38
col 1; len 2; (2):  c1 0f
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#18[7410] flag: K-----, lock: 0, len=32
col 0; len 18; (18):  53 59 53 5f 49 4f 54 5f 54 4f 50 5f 32 36 32 34 37 30
col 1; len 2; (2):  c1 12
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#19[7378] flag: K-----, lock: 0, len=32
col 0; len 18; (18):  53 59 53 5f 49 4f 54 5f 54 4f 50 5f 32 36 32 34 37 32
col 1; len 2; (2):  c1 15
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#20[7346] flag: K-----, lock: 0, len=32
col 0; len 18; (18):  53 59 53 5f 49 4f 54 5f 54 4f 50 5f 32 36 32 34 37 35
col 1; len 2; (2):  c1 17
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#21[7307] flag: K-----, lock: 0, len=39
col 0; len 25; (25): 
 53 59 53 5f 4c 4f 42 30 30 30 30 32 36 32 34 35 39 43 30 30 30 33 30 24 24
col 1; len 2; (2):  c1 08
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#22[7007] flag: K-----, lock: 2, len=21
col 0; len 8; (8):  54 45 53 54 5f 49 4f 54
col 1; len 1; (1):  80
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#23[7285] flag: K--D--, lock: 2, len=22
col 0; len 8; (8):  54 45 53 54 5f 49 4f 54
col 1; len 2; (2):  c1 02
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#24[7253] flag: K-----, lock: 0, len=32
col 0; len 18; (18):  54 45 53 54 5f 4d 45 53 53 41 47 45 5f 54 41 42 4c 45
col 1; len 2; (2):  c1 1f
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#25[7226] flag: K-----, lock: 0, len=27
col 0; len 13; (13):  54 45 53 54 5f 4d 53 47 5f 54 59 50 45
col 1; len 2; (2):  c1 07
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#26[7202] flag: K-----, lock: 0, len=24
col 0; len 10; (10):  54 45 53 54 5f 51 55 45 55 45
col 1; len 2; (2):  c1 1e
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#27[7159] flag: K-----, lock: 0, len=43
col 0; len 29; (29): 
 54 45 53 54 5f 51 55 45 55 45 5f 43 41 4c 4c 42 41 43 4b 5f 50 52 4f 43 45
 44 55 52 45
col 1; len 2; (2):  c1 03
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#28[7133] flag: K-----, lock: 0, len=26
col 0; len 12; (12):  54 45 53 54 5f 51 55 45 55 45 5f 4e
col 1; len 2; (2):  c1 1c
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#29[7107] flag: K-----, lock: 0, len=26
col 0; len 12; (12):  54 45 53 54 5f 51 55 45 55 45 5f 52
col 1; len 2; (2):  c1 1b
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#30[7077] flag: K-----, lock: 0, len=30
col 0; len 16; (16):  54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45
col 1; len 2; (2):  c1 10
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
row#31[6988] flag: K-----, lock: 2, len=19
col 0; len 5; (5):  68 65 6c 6c 6f
col 1; len 2; (2):  c1 02
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c
----- end of leaf block dump -----

现在从中随便摘取第26行(row#26)来分析，片段如下，

row#26[7202] flag: K-----, lock: 0, len=24
col 0; len 10; (10):  54 45 53 54 5f 51 55 45 55 45
col 1; len 2; (2):  c1 1e
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  01 00 25 5c

从中可以很清楚看到index条目中包含两列 col 0, col 1。 可以先猜测下第一列应该就是index IDX_TEST_IOT_1的索引列 Name， 那么col 1是什么呢，之前提到过IOT的secondary index中应该是包含了主键的，因此这个col 1很有可能就是对应的主键ID这一列。 因为IDX_TEST_IOT_1是根据Name来创建的，默认是升序排列的，那么很容易可以猜测这个索引item对应表中的哪一行数据，如下所示...

SQL> select id, name from (select row_number()over(order by name) rn, id, name from test_iot) where rn=26;

        ID NAME
---------- ------------------------------
        29 TEST_QUEUE

现在用dump函数检测下是否正确，

SQL> select dump('TEST_QUEUE', 16) "col 0", dump(29, 16) "col 1" from dual;

col 0                                        col 1
-------------------------------------------- ------------------
Typ=96 Len=10: 54,45,53,54,5f,51,55,45,55,45 Typ=2 Len=2: c1,1e

仔细瞅瞅，完全一样，这就说明了IOT的secondary index中确实包含了primary key!

至于最后一行的 col 0: [ 4] 01 00 25 5c

不是很清楚，个人猜测应该是所谓的"physical guess", 指向IOT表的"physical rowid",  [4] 表示长度， 但是很奇怪地发现上面dump中的每一个row的最后一行都一样！！这个待验证！！！