<pre name="code" class="html">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_id from user_objects;
OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T2 102696
SYS_IOT_TOP_102696 102697
begin
for i in 1 .. 2000
loop
insert into t2 values(i,i+1,i+2,'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 102697';
会话已更改。
----- begin tree dump
branch: 0x10000eb 16777451 (0: nrow: 12, level: 1)
leaf: 0x10000ec 16777452 (-1: nrow: 184 rrow: 184)
leaf: 0x10000ed 16777453 (0: nrow: 181 rrow: 181)
leaf: 0x10000ee 16777454 (1: nrow: 181 rrow: 181)
leaf: 0x10000ef 16777455 (2: nrow: 181 rrow: 181)
leaf: 0x10000f4 16777460 (3: nrow: 181 rrow: 181)
leaf: 0x10000f5 16777461 (4: nrow: 181 rrow: 181)
leaf: 0x10000f6 16777462 (5: nrow: 181 rrow: 181)
leaf: 0x10000f7 16777463 (6: nrow: 181 rrow: 181)
leaf: 0x10000f0 16777456 (7: nrow: 181 rrow: 181)
leaf: 0x10000f1 16777457 (8: nrow: 181 rrow: 181)
leaf: 0x10000f2 16777458 (9: nrow: 181 rrow: 181)
leaf: 0x10000f3 16777459 (10: nrow: 6 rrow: 6)
----- end tree dump
SQL> select dbms_utility.data_block_address_file(16777452)fno,
dbms_utility.data_block_address_block(16777452) bkno from dual 2 ;
FNO BKNO
---------- ----------
4 236
alter system dump datafile 4 block 236;
declare
n number;
begin
dbms_stats.convert_raw_value('c102',
n);
dbms_output.put_line(n);
end;
DECLARE
n VARCHAR2 (2000);
BEGIN
DBMS_STATS.convert_raw_value ('34', 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);
DBMS_STATS.convert_raw_value ('20', n);
DBMS_OUTPUT.put_line (n);
END;
dump 信息如下；
row#0[404] flag: K-----, lock: 0, len=41
col 0; len 2; (2): c1 02 ---1
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [10] 32 20 20 20 20 20 20 20 20 20 --2
col 1: [10] 33 20 20 20 20 20 20 20 20 20 --3
col 2: [10] 61 31 20 20 20 20 20 20 20 20 --a1
row#1[445] flag: K-----, lock: 0, len=41
col 0; len 2; (2): c1 03 --2
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [10] 33 20 20 20 20 20 20 20 20 20 --3
col 1: [10] 34 20 20 20 20 20 20 20 20 20 --4
col 2: [10] 61 32 20 20 20 20 20 20 20 20 --a2
row#2[486] flag: K-----, lock: 0, len=41
col 0; len 2; (2): c1 04 --3
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [10] 34 20 20 20 20 20 20 20 20 20 --4
col 1: [10] 35 20 20 20 20 20 20 20 20 20 --5
col 2: [10] 61 33 20 20 20 20 20 20 20 20 --a3
row#3[527] flag: K-----, lock: 0, len=41
col 0; len 2; (2): c1 05
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [10] 35 20 20 20 20 20 20 20 20 20
col 1: [10] 36 20 20 20 20 20 20 20 20 20
col 2: [10] 61 34 20 20 20 20 20 20 20 20
row#4[568] flag: K-----, lock: 0, len=41
col 0; len 2; (2): c1 06
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [10] 36 20 20 20 20 20 20 20 20 20
col 1: [10] 37 20 20 20 20 20 20 20 20 20
col 2: [10] 61 35 20 20 20 20 20 20 20 20
row#5[609] flag: K-----, lock: 0, len=41
col 0; len 2; (2): c1 07
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [10] 37 20 20 20 20 20 20 20 20 20
col 1: [10] 38 20 20 20 20 20 20 20 20 20
col 2: [10] 61 36 20 20 20 20 20 20 20 20
row#6[650] flag: K-----, lock: 0, len=41
col 0; len 2; (2): c1 08
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [10] 38 20 20 20 20 20 20 20 20 20
col 1: [10] 39 20 20 20 20 20 20 20 20 20
col 2: [10] 61 37 20 20 20 20 20 20 20 20
row#7[691] flag: K-----, lock: 0, len=41
col 0; len 2; (2): c1 09 --8
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [10] 39 20 20 20 20 20 20 20 20 20
col 1: [10] 31 30 20 20 20 20 20 20 20 20
col 2: [10] 61 38 20 20 20 20 20 20 20 20
row#8[732] flag: K-----, lock: 0, len=41
col 0; len 2; (2): c1 0a --9
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [10] 31 30 20 20 20 20 20 20 20 20
col 1: [10] 31 31 20 20 20 20 20 20 20 20
col 2: [10] 61 39 20 20 20 20 20 20 20 20
row#9[773] flag: K-----, lock: 0, len=41
col 0; len 2; (2): c1 0b --10
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [10] 31 31 20 20 20 20 20 20 20 20
col 1: [10] 31 32 20 20 20 20 20 20 20 20
col 2: [10] 61 31 30 20 20 20 20 20 20 20
row#10[814] flag: K-----, lock: 0, len=41
col 0; len 2; (2): c1 0c --11
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [10] 31 32 20 20 20 20 20 20 20 20
col 1: [10] 31 33 20 20 20 20 20 20 20 20
col 2: [10] 61 31 31 20 20 20 20 20 20 20
row#11[855] flag: K-----, lock: 0, len=41
col 0; len 2; (2): c1 0d --12
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [10] 31 33 20 20 20 20 20 20 20 20
col 1: [10] 31 34 20 20 20 20 20 20 20 20
col 2: [10] 61 31 32 20 20 20 20 20 20 20
row#12[896] flag: K-----, lock: 0, len=41
col 0; len 2; (2): c1 0e --13
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [10] 31 34 20 20 20 20 20 20 20 20 --14
col 1: [10] 31 35 20 20 20 20 20 20 20 20 --15
col 2: [10] 61 31 33 20 20 20 20 20 20 20 --a13