iot表测试: 在create table语句后面使用organization index,就指定数据表创建结构是IOT。但是在不指定主键Primary Key的情况下,是不允许建表的。 create table t2 (id int primary key,a1 char(10),a2 char(10),a3 char(10))organization index; SQL> select object_name,object_type from user_objects; OBJECT_NAME OBJECT_TYPE -------------------------------------------------------------------------------------------------------------------------------- ------------------- T1 TABLE SYS_C0021515 SQL> select object_name,object_type from user_objects; OBJECT_NAME OBJECT_TYPE -------------------------------------------------------------------------------------------------------------------------------- ------------------- SYS_IOT_TOP_101447 INDEX T2 TABLE SYS_C0021515 INDEX T1 SQL> select segment_name,SEGMENT_TYPE,TABLESPACE_NAME,bytes,blocks,extents from user_segments; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES BLOCKS EXTENTS ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- SYS_C0021515 INDEX USERS 131072 16 2 SYS_IOT_TOP_101447 INDEX USERS 196608 24 3 T1 TABLE USERS 196608 24 3 可以IOT表数据都存放在索引里 SQL> select table_name,TABLESPACE_NAME,CLUSTER_NAME,IOT_NAME from user_tables; TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME --------------- ------------------------------ ------------------------------ ------------------------------ T2 T1 USERS SQL> select segment_name,SEGMENT_TYPE, TABLESPACE_NAME,EXTENT_ID,bytes,blocks from user_extents; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS -------------------- ------------------ ------------------------------ ---------- ---------- ---------- T1 TABLE USERS 0 65536 8 T1 TABLE USERS 1 65536 8 T1 TABLE USERS 2 65536 8 SYS_C0021515 INDEX USERS 0 65536 8 SYS_C0021515 INDEX USERS 1 65536 8 SYS_IOT_TOP_101447 INDEX USERS 0 65536 8 SYS_IOT_TOP_101447 INDEX USERS 1 65536 8 SYS_IOT_TOP_101447 INDEX USERS 2 65536 8 已选择8行。 可以看到根本没有给t2(iot)表分配空间 SQL> set linesize 200 SQL> select object_name,object_id from user_objects; OBJECT_NAME OBJECT_ID -------------------------------------------------------------------------------------------------------------------------------- ---------- SYS_IOT_TOP_101449 101450 T2 101449 begin for i in 1 .. 2000 loop insert into t2 values(i,i,i,'a'||i); end loop ; commit; end; 将索引dump到trace文件中 SQL> select value from v$diag_info where name='Default Trace File'; VALUE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/oadb/oadb/trace/oadb_ora_29729.trc SQL> alter session set events 'immediate trace name treedump level 101450'; 会话已更改。 ----- begin tree dump branch: 0x10000ab 16777387 (0: nrow: 12, level: 1) leaf: 0x10000ac 16777388 (-1: nrow: 184 rrow: 184) leaf: 0x10000ad 16777389 (0: nrow: 181 rrow: 181) leaf: 0x10000ae 16777390 (1: nrow: 181 rrow: 181) leaf: 0x10000af 16777391 (2: nrow: 181 rrow: 181) leaf: 0x10000b4 16777396 (3: nrow: 181 rrow: 181) leaf: 0x10000b5 16777397 (4: nrow: 181 rrow: 181) leaf: 0x10000b6 16777398 (5: nrow: 181 rrow: 181) leaf: 0x10000b7 16777399 (6: nrow: 181 rrow: 181) leaf: 0x10000b0 16777392 (7: nrow: 181 rrow: 181) leaf: 0x10000b1 16777393 (8: nrow: 181 rrow: 181) leaf: 0x10000b2 16777394 (9: nrow: 181 rrow: 181) leaf: 0x10000b3 16777395 (10: nrow: 6 rrow: 6) ----- end tree dump ~ SQL> select dbms_utility.data_block_address_file(16777388)fno, dbms_utility.data_block_address_block(16777388) bkno from dual 2 ; FNO BKNO ---------- ---------- 4 172 alter system dump datafile 4 block 172; dump 信息如下; tab 0, row 0, @0x1f69 tl: 47 fb: --H-FL-- lb: 0x0 cc: 4 col 0: [10] 36 31 20 20 20 20 20 20 20 20 col 1: [10] 36 31 20 20 20 20 20 20 20 20 col 2: [10] 36 31 20 20 20 20 20 20 20 20 col 3: [10] 61 36 31 20 20 20 20 20 20 20 tab 0, row 1, @0x1f3a tl: 47 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [10] 31 30 30 20 20 20 20 20 20 20 col 1: [10] 31 30 30 20 20 20 20 20 20 20 col 2: [10] 31 30 30 20 20 20 20 20 20 20 col 3: [10] 61 31 30 30 20 20 20 20 20 20 tab 0, row 2, @0x1f0b tl: 47 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [10] 31 30 31 20 20 20 20 20 20 20 col 1: [10] 31 30 31 20 20 20 20 20 20 20 col 2: [10] 31 30 31 20 20 20 20 20 20 20 col 3: [10] 61 31 30 31 20 20 20 20 20 20 tab 0, row 3, @0x1edc tl: 47 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [10] 31 30 32 20 20 20 20 20 20 20 col 1: [10] 31 30 32 20 20 20 20 20 20 20 col 2: [10] 31 30 32 20 20 20 20 20 20 20 col 3: [10] 61 31 30 32 20 20 20 20 20 20 tab 0, row 4, @0x1ead tl: 47 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [10] 31 30 33 20 20 20 20 20 20 20 col 1: [10] 31 30 33 20 20 20 20 20 20 20 col 2: [10] 31 30 33 20 20 20 20 20 20 20 col 3: [10] 61 31 30 33 20 20 20 20 20 20 tab 0, row 5, @0x1e7e tl: 47 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [10] 31 30 34 20 20 20 20 20 20 20 col 1: [10] 31 30 34 20 20 20 20 20 20 20 col 2: [10] 31 30 34 20 20 20 20 20 20 20 col 3: [10] 61 31 30 34 20 20 20 20 20 20 DECLARE n VARCHAR2 (2000); BEGIN DBMS_STATS.convert_raw_value ('36', n); DBMS_OUTPUT.put_line (n); DBMS_STATS.convert_raw_value ('31', n); DBMS_OUTPUT.put_line (n); DBMS_STATS.convert_raw_value ('20', n); DBMS_OUTPUT.put_line (n); DBMS_STATS.convert_raw_value ('20', n); DBMS_OUTPUT.put_line (n); DBMS_STATS.convert_raw_value ('20', n); DBMS_OUTPUT.put_line (n); DBMS_STATS.convert_raw_value ('20', n); DBMS_OUTPUT.put_line (n); DBMS_STATS.convert_raw_value ('20', n); DBMS_OUTPUT.put_line (n); DBMS_STATS.convert_raw_value ('20', n); DBMS_OUTPUT.put_line (n); DBMS_STATS.convert_raw_value ('20', n); DBMS_OUTPUT.put_line (n); DBMS_STATS.convert_raw_value ('20', n); DBMS_OUTPUT.put_line (n); END; tab 0, row 0, @0x1f69 tl: 47 fb: --H-FL-- lb: 0x0 cc: 4 col 0: [10] 36 31 20 20 20 20 20 20 20 20 表示61 col 1: [10] 36 31 20 20 20 20 20 20 20 20 表示61 col 2: [10] 36 31 20 20 20 20 20 20 20 20 表示61 col 3: [10] 61 36 31 20 20 20 20 20 20 20 表示a61 tab 0, row 1, @0x1f3a tl: 47 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [10] 31 30 30 20 20 20 20 20 20 20 表示100 col 1: [10] 31 30 30 20 20 20 20 20 20 20 表示100 col 2: [10] 31 30 30 20 20 20 20 20 20 20 表示100 col 3: [10] 61 31 30 30 20 20 20 20 20 20 表示a100 tab 0, row 2, @0x1f0b tl: 47 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [10] 31 30 31 20 20 20 20 20 20 20 表示101 col 1: [10] 31 30 31 20 20 20 20 20 20 20 表示101 col 2: [10] 31 30 31 20 20 20 20 20 20 20 表示101 col 3: [10] 61 31 30 31 20 20 20 20 20 20 表示a101 tab 0, row 3, @0x1edc tl: 47 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [10] 31 30 32 20 20 20 20 20 20 20 表示102 col 1: [10] 31 30 32 20 20 20 20 20 20 20 表示102 col 2: [10] 31 30 32 20 20 20 20 20 20 20 表示102 col 3: [10] 61 31 30 32 20 20 20 20 20 20 表示a102 tab 0, row 4, @0x1ead tl: 47 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [10] 31 30 33 20 20 20 20 20 20 20 表示103 col 1: [10] 31 30 33 20 20 20 20 20 20 20 表示103 col 2: [10] 31 30 33 20 20 20 20 20 20 20 表示103 col 3: [10] 61 31 30 33 20 20 20 20 20 20 表示a103 tab 0, row 5, @0x1e7e tl: 47 fb: --H-FL-- lb: 0x2 cc: 4 col 0: [10] 31 30 34 20 20 20 20 20 20 20 表示104 col 1: [10] 31 30 34 20 20 20 20 20 20 20 表示104 col 2: [10] 31 30 34 20 20 20 20 20 20 20 表示104 col 3: [10] 61 31 30 34 20 20 20 20 20 20 表示a104