索引组织表(IOT)中UROWID存储追踪

--IOT create table iot  (  x    int,     y    date,     z    varchar2(2000),     constraint iot_pk primary key (x))organization indexpctthreshold 10overflow/EODA@PROD1> insert into iot values (1,to_date('1995-02-01','yyyy-mm-dd'),'avc');1 row created.EODA@PROD1> insert into iot values (2,to_date('1995-02-01','yyyy-mm-dd'),'eee');1 row created.EODA@PROD1> insert into iot values (3,to_date('1995-02-01','yyyy-mm-dd'),'uij');1 row created.EODA@PROD1> select rowid, dump(rowid) from iot;ROWID-----------------------------------------DUMP(ROWID)----------------------------------------------------------------------------------------------------*BAECZHMCwQL+Typ=208 Len=10: 2,4,1,2,100,115,2,193,2,254   --TYP208类型为UROWID， UROWID(可以称为通用ROWID，逻辑ROWID): 表的行地址，表指的是index-organized tables。IOT中物理rowid是可能变化的，另外Oracle要依靠rowid来建立表的索引，所以对IOT表来物理rowid就不行了。*BAECZHMCwQP+Typ=208 Len=10: 2,4,1,2,100,115,2,193,3,254*BAECZHMCwQT+Typ=208 Len=10: 2,4,1,2,100,115,2,193,4,254EODA@PROD1> select table_name,tablespace_name from user_tables where table_name like '%IOT%';TABLE_NAME       TABLESPACE_NAME------------------------------ ------------------------------SYS_IOT_OVER_85886       USERSIOT                                        --没有被分配表空间，说明Oracle并不认为IOT是一个表。IOT_ADDRESSESEODA@PROD1> select index_name, index_type, table_name, PCT_THRESHOLD, CLUSTERING_FACTOR from user_indexes where table_name like '%IOT%';  --寻找表上的索引INDEX_NAME       INDEX_TYPE   TABLE_NAME  PCT_THRESHOLD CLUSTERING_FACTOR------------------------------ --------------------------- ------------------------------ ------------- -----------------SYS_IOT_TOP_85796       IOT - TOP   IOT_ADDRESSES     50 0IOT_PK       IOT - TOP   IOT     10     10            EODA@PROD1> select object_id, object_name from dba_objects where object_name like 'IOT'; OBJECT_ID OBJECT_NAME---------- --------------------------------------------------------------------------------------------------------------------------------     85886 IOT 85888 IOT_PK   --找到OBJECT_IDEODA@PROD1> alter session set events 'immediate trace name treedump level 85888';   --使用treedump事件查看索引结构Session altered./* ----- begin tree dumpleaf: 0x1026473 16934003 (0: nrow: 3 rrow: 3)  --0x1026473 16进制索引叶块地址----- end tree dump */EODA@PROD1> variable file# numberEODA@PROD1> variable block# number EODA@PROD1> EODA@PROD1> execute :file#:=dbms_utility.data_block_address_file(to_number('1026473','xxxxxxxxxx'));PL/SQL procedure successfully completed.EODA@PROD1> execute :block#:=dbms_utility.data_block_address_block(to_number('1026473','xxxxxxxxxx'));  --转换得到file#,block#PL/SQL procedure successfully completed.EODA@PROD1> print file#     FILE#---------- 4EODA@PROD1> print block#    BLOCK#----------    156787SYS@PROD1> alter system dump datafile 4 block 156787;  --dump出来查看System altered./* Leaf block dump===============header address 12420708=0xbd8664kdxcolev 0KDXCOLEV Flags = - - -kdxcolok 0kdxcoopc 0x90: opcode=0: iot flags=I-- is converted=Ykdxconco 1kdxcosdc 0kdxconro 3kdxcofbo 42=0x2akdxcofeo 7972=0x1f24kdxcoavs 7930kdxlespl 0kdxlende 0kdxlenxt 0=0x0kdxleprv 0=0x0kdxledsz 0kdxlebksz 8032row#0[8012] flag: K-----, lock: 0, len=20  --row#0,row#1,row#2存放着先前插入的三条数据col 0; len 2; (2):  c1 02tl: 15 fb: --H-FL-- lb: 0x0  cc: 2col  0: [ 7]  77 c3 02 01 01 01 01  --日期格式1995-02-01col  1: [ 3]  61 76 63            --十六进制转换为ASCII avcrow#1[7992] flag: K-----, lock: 2, len=20col 0; len 2; (2):  c1 03tl: 15 fb: --H-FL-- lb: 0x0  cc: 2col  0: [ 7]  77 c3 02 01 01 01 01  --日期格式1995-02-01col  1: [ 3]  65 65 65--eeerow#2[7972] flag: K-----, lock: 2, len=20col 0; len 2; (2):  c1 04tl: 15 fb: --H-FL-- lb: 0x0  cc: 2col  0: [ 7]  77 c3 02 01 01 01 01  --日期格式1995-02-01col  1: [ 3]  75 69 6a--uij----- end of leaf block dump ----- */