heap 和iot 对比
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------------------
T2 TABLE
SYS_C0022204 INDEX
T1 TABLE
SYS_IOT_TOP_102688 INDEX
OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T2 102688
SYS_C0022204 102687
T1 102686
SYS_IOT_TOP_102688 102689
select value from v$diag_info where name='Default Trace File';
alter session set events 'immediate trace name treedump level 102687';
----- begin tree dump
branch: 0x10000c3 16777411 (0: nrow: 5, level: 1)
leaf: 0x10000c6 16777414 (-1: nrow: 193 rrow: 193)
leaf: 0x10000c7 16777415 (0: nrow: 189 rrow: 189)
leaf: 0x10000c4 16777412 (1: nrow: 189 rrow: 189)
leaf: 0x10000c5 16777413 (2: nrow: 188 rrow: 188)
leaf: 0x10000ca 16777418 (3: nrow: 244 rrow: 243)
----- end tree dump
select dbms_utility.data_block_address_file(16777418) fno,
dbms_utility.data_block_address_block(16777418) bkno
from dual ;
SQL> select dbms_utility.data_block_address_file(16777418) fno,
dbms_utility.data_block_address_block(16777418) bkno
from dual ; 2 3
FNO BKNO
---------- ----------
4 202
alter system dump datafile 4 block 202;
row#0[4365] flag: ------, lock: 0, len=19, data:(6): 01 00 00 bd 00 4d
col 0; len 10; (10): 37 38 20 20 20 20 20 20 20 20 --78
row#1[4384] flag: ------, lock: 0, len=19, data:(6): 01 00 00 bc 00 0f
col 0; len 10; (10): 37 38 30 20 20 20 20 20 20 20 --780
row#2[4403] flag: ------, lock: 0, len=19, data:(6): 01 00 00 bc 00 10
col 0; len 10; (10): 37 38 31 20 20 20 20 20 20 20 --781
row#3[4422] flag: ------, lock: 0, len=19, data:(6): 01 00 00 bc 00 11
col 0; len 10; (10): 37 38 32 20 20 20 20 20 20 20 --782
row#4[4441] flag: ------, lock: 0, len=19, data:(6): 01 00 00 bc 00 12
col 0; len 10; (10): 37 38 33 20 20 20 20 20 20 20
row#5[4460] flag: ------, lock: 0, len=19, data:(6): 01 00 00 bc 00 13
col 0; len 10; (10): 37 38 34 20 20 20 20 20 20 20
row#227[3681] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 1c
col 0; len 10; (10): 39 38 34 20 20 20 20 20 20 20 --984
row#228[3662] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 1d
col 0; len 10; (10): 39 38 35 20 20 20 20 20 20 20
row#229[3643] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 1e
col 0; len 10; (10): 39 38 36 20 20 20 20 20 20 20
row#230[3624] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 1f
col 0; len 10; (10): 39 38 37 20 20 20 20 20 20 20
row#231[3605] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 20
col 0; len 10; (10): 39 38 38 20 20 20 20 20 20 20
row#232[3586] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 21
col 0; len 10; (10): 39 38 39 20 20 20 20 20 20 20
row#233[8013] flag: ------, lock: 0, len=19, data:(6): 01 00 00 bd 00 62
col 0; len 10; (10): 39 39 20 20 20 20 20 20 20 20 --99
row#234[3567] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 22
col 0; len 10; (10): 39 39 30 20 20 20 20 20 20 20 --990
row#235[3548] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 23
col 0; len 10; (10): 39 39 31 20 20 20 20 20 20 20 --991
row#236[3529] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 24
col 0; len 10; (10): 39 39 32 20 20 20 20 20 20 20 --992
row#237[3510] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 25
col 0; len 10; (10): 39 39 33 20 20 20 20 20 20 20 --993
row#238[3491] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 26
col 0; len 10; (10): 39 39 34 20 20 20 20 20 20 20
row#239[3472] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 27
col 0; len 10; (10): 39 39 35 20 20 20 20 20 20 20
row#240[3453] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 28
col 0; len 10; (10): 39 39 36 20 20 20 20 20 20 20
row#241[3434] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 29
col 0; len 10; (10): 39 39 37 20 20 20 20 20 20 20
row#242[3415] flag: ------, lock: 0, len=19, data:(6): 01 00 00 d2 00 2a
col 0; len 10; (10): 39 39 38 20 20 20 20 20 20 20 --998
row#243[3396] flag: ---D--, lock: 2, len=19, data:(6): 01 00 00 d2 00 2b
col 0; len 10; (10): 39 39 39 20 20 20 20 20 20 20 --999
heap表的排序顺序；
ID A1 A2
---------- ---------- ----------
988 988 a988
989 989 a989
99 99 a99
990 990 a990
991 991 a991
992 992 a992
993 993 a993
994 994 a994
995 995 a995
996 996 a996
997 997 a997
iot 表；
select value from v$diag_info where name='Default Trace File';
alter session set events 'immediate trace name treedump level 102689';
----- begin tree dump
branch: 0x10000ab 16777387 (0: nrow: 5, level: 1)
leaf: 0x10000ae 16777390 (-1: nrow: 245 rrow: 245)
leaf: 0x10000af 16777391 (0: nrow: 242 rrow: 242)
leaf: 0x10000ac 16777388 (1: nrow: 242 rrow: 242)
leaf: 0x10000ad 16777389 (2: nrow: 242 rrow: 242)
leaf: 0x10000b2 16777394 (3: nrow: 31 rrow: 31)
----- end tree dump
~
SQL> select dbms_utility.data_block_address_file(16777394) fno,
dbms_utility.data_block_address_block(16777394) bkno
from dual ; 2 3
FNO BKNO
---------- ----------
4 178
alter system dump datafile 4 block 178;
row#0[8001] flag: K-----, lock: 0, len=31
col 0; len 3; (3): c2 0a 49
tl: 25 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [10] 39 37 32 20 20 20 20 20 20 20 --972
col 1: [10] 61 39 37 32 20 20 20 20 20 20 --a972
row#1[7970] flag: K-----, lock: 0, len=31
col 0; len 3; (3): c2 0a 4a
tl: 25 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [10] 39 37 33 20 20 20 20 20 20 20
col 1: [10] 61 39 37 33 20 20 20 20 20 20
row#2[7939] flag: K-----, lock: 0, len=31
col 0; len 3; (3): c2 0a 4b
tl: 25 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [10] 39 37 34 20 20 20 20 20 20 20
col 1: [10] 61 39 37 34 20 20 20 20 20 20
row#3[7908] flag: K-----, lock: 0, len=31
col 0; len 3; (3): c2 0a 4c
tl: 25 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [10] 39 37 35 20 20 20 20 20 20 20
col 1: [10] 61 39 37 35 20 20 20 20 20 20
row#4[7877] flag: K-----, lock: 0, len=31
col 0; len 3; (3): c2 0a 4d
tl: 25 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [10] 39 37 36 20 20 20 20 20 20 20
col 1: [10] 61 39 37 36 20 20 20 20 20 20
row#5[7846] flag: K-----, lock: 0, len=31
col 0; len 3; (3): c2 0a 4e
tl: 25 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [10] 39 37 37 20 20 20 20 20 20 20
col 1: [10] 61 39 37 37 20 20 20 20 20 20
row#6[7815] flag: K-----, lock: 0, len=31
col 0; len 3; (3): c2 0a 4f
tl: 25 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [10] 39 37 38 20 20 20 20 20 20 20
col 1: [10] 61 39 37 38 20 20 20 20 20 20
row#7[7784] flag: K-----, lock: 0, len=31
col 0; len 3; (3): c2 0a 50
tl: 25 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [10] 39 37 39 20 20 20 20 20 20 20 --979
col 1: [10] 61 39 37 39 20 20 20 20 20 20
row#8[7753] flag: K-----, lock: 0, len=31
col 0; len 3; (3): c2 0a 51
tl: 25 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [10] 39 38 30 20 20 20 20 20 20 20 --980
col 1: [10] 61 39 38 30 20 20 20 20 20 20
row#9[7722] flag: K-----, lock: 0, len=31
col 0; len 3; (3): c2 0a 52
tl: 25 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [10] 39 38 31 20 20 20 20 20 20 20 --981
col 1: [10] 61 39 38 31 20 20 20 20 20 20
row#10[7691] flag: K-----, lock: 0, len=31
col 0; len 3; (3): c2 0a 53
tl: 25 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [10] 39 38 32 20 20 20 20 20 20 20 -982
col 1: [10] 61 39 38 32 20 20 20 20 20 20
iot表排序顺序:
972 972 a972
973 973 a973
974 974 a974
975 975 a975
976 976 a976
977 977 a977
978 978 a978
979 979 a979
---------- ---------- ----------
980 980 a980
981 981 a981
982 982 a982
983 983 a983
984 984 a984
985 985 a985
986 986 a986
987 987 a987
988 988 a988
989 989 a989
990 990 a990
ID A1 A2
---------- ---------- ----------
991 991 a991
992 992 a992
993 993 a993
994 994 a994
995 995 a995
996 996 a996
997 997 a997
998 998 a998
heap表和iot表排序规则不同
