Oracle dump索引组织表

  dump了索引组织表，发现跟索引的块结构是一样的。

create table iot_t 

(
  id number,
  name varchar2(10),
  primary key(id)
)
organization index;


insert into iot_t values(1,'a');
insert into iot_t values(5,'e');
commit;

看到传统的找数据块的方法并不奏效
SQL> select rowid,
           dbms_rowid.rowid_object(rowid) object_id,
           dbms_rowid.rowid_relative_fno(rowid) file_id,
           dbms_rowid.rowid_block_number(rowid) block_id,
           dbms_rowid.rowid_row_number(rowid) num
      from iot_t;
       dbms_rowid.rowid_row_number(rowid) num
       *
第 5 行出现错误:
ORA-06553: PLS-306: 调用 'ROWID_ROW_NUMBER' 时参数个数或类型错误

看到它根本不是表
SQL> select  table_name, tablespace_name, NUM_ROWS, BLOCKS, EMPTY_BLOCKS
      from user_tables
     where lower(table_name) in ('iot_t');
TABLE_NAME TABLESPACE_NAME    NUM_ROWS  BLOCKS EMPTY_BLOCKS
---------- ---------------- ---------- ------- ------------
IOT_T

索引组织表就是索引
SQL> select INDEX_NAME,
           INDEX_TYPE,
           TABLE_NAME,
           PCT_THRESHOLD,
           INCLUDE_COLUMN
      from dba_indexes
     where lower(table_name) in ('iot_t');
INDEX_NAME          INDEX_TYPE    TABLE_NAMEPCT_ THRESHOLD INCLUDE_COLUMN
------------------- ------------- -------------- --------- --------------
SYS_IOT_TOP_77544   IOT - TOP     IOT_T              50         0

按照索引的方式dump
SQL>  select OBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE
       from dba_objects
      where object_name in ('SYS_IOT_TOP_77544', 'IOT_T');
OBJECT_NAME        OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------- ---------- -------------- ---
IOT_T                  77544                TABLE
SYS_IOT_TOP_77544      77545          77545 INDEX

alter session set events 'immediate trace name treedump level 77545';
dump索引出来的结果是：
----- begin tree dump
leaf: 0x1001533 16782643 (0: nrow: 2 rrow: 2)
----- end tree dump

定位出是哪个文件，哪个数据块
SQL>  select dbms_utility.data_block_address_file(16782643) "file",
           dbms_utility.data_block_address_block(16782643) "block"
      from dual;
      file      block
---------- ----------
         4       5427      
alter system  checkpoint;
alter system dump datafile 4 block 5427;

dump出来的数据块结果是：

Start dump data blocks tsn: 4 file#:4 minblk 5427 maxblk 5427
................................................
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0007.017.000018c4  0x00c008ee.0193.04  --U-    1  fsc 0x0000.e2b70a42
...........................................................
row#0[8026] flag: K-----, lock: 0, len=10  --第一行数据 c102就是1，61就是a
col 0; len 2; (2):  c1 02   
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 1]  61
row#1[8016] flag: K-----, lock: 2, len=10  --第一行数据 c106就是5，65就是e
col 0; len 2; (2):  c1 06
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 1]  65
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 5427 maxblk 5427

插入一条数据之后：
insert into iot_t values(2,'b');
commit;
alter system  checkpoint;
alter system dump datafile 4 block 5427;

dump出来的数据块结果是：
Start dump data blocks tsn: 4 file#:4 minblk 5427 maxblk 5427
Block header dump:  0x01001533
 Object id on Block? Y
 seg/obj: 0x12ee9  csc: 0xcb5.e2b70e52  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1001530 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0005.01c.000018d0  0x00c00411.0140.03  --U-    1  fsc 0x0000.e2b70e53
........................................................
row#0[8026] flag: K-----, lock: 0, len=10
col 0; len 2; (2):  c1 02
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 1]  61
row#1[8006] flag: K-----, lock: 2, len=10 --插入到1和5之间
col 0; len 2; (2):  c1 03
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 1]  62
row#2[8016] flag: K-----, lock: 0, len=10
col 0; len 2; (2):  c1 06
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 1]  65
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 5427 maxblk 5427