Oracle dump 分析secondary key

时间：2021-07-10 16:43:16
验证secondary key 含有主键列数据

SQL>  select object_name,object_id,OBJECT_TYPE  from user_objects;

OBJECT_NAME	      OBJECT_ID OBJECT_TYPE

-------------------- ---------- -------------------

T2			 101449 TABLE

SYS_IOT_TOP_101449	 101450 INDEX

T2_IDX1 		 101451 INDEX

将索引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_5435.trc

SQL> alter session set events 'immediate trace name treedump level 101451';

会话已更改。

----- begin tree dump

branch: 0x10000bb 16777403 (0: nrow: 8, level: 1)

   leaf: 0x10000bc 16777404 (-1: nrow: 266 rrow: 266)

   leaf: 0x10000bd 16777405 (0: nrow: 266 rrow: 266)

   leaf: 0x10000be 16777406 (1: nrow: 266 rrow: 266)

   leaf: 0x10000bf 16777407 (2: nrow: 266 rrow: 266)

   leaf: 0x10000c0 16777408 (3: nrow: 266 rrow: 266)

   leaf: 0x10000c1 16777409 (4: nrow: 267 rrow: 267)

   leaf: 0x10000c2 16777410 (5: nrow: 266 rrow: 266)

   leaf: 0x10000c3 16777411 (6: nrow: 137 rrow: 137)

----- end tree dump

SQL> select dbms_utility.data_block_address_file(16777404)fno,

dbms_utility.data_block_address_block(16777404) bkno from dual   2  ;

       FNO	 BKNO

---------- ----------

	 4	  188

alter system dump datafile 4 block 188;

row#0[8008] flag: K-----, lock: 0, len=24

col 0; len 10; (10):  61 31 20 20 20 20 20 20 20 20

col 1; len 2; (2):  c1 02

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 00 ac

row#1[7984] flag: K-----, lock: 0, len=24

col 0; len 10; (10):  61 31 30 20 20 20 20 20 20 20

col 1; len 2; (2):  c1 0b

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 00 ac

row#2[7960] flag: K-----, lock: 0, len=24

col 0; len 10; (10):  61 31 30 30 20 20 20 20 20 20

col 1; len 2; (2):  c2 02

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 00 ac

row#3[7936] flag: K-----, lock: 0, len=24

col 0; len 10; (10):  61 31 30 30 30 20 20 20 20 20

col 1; len 2; (2):  c2 0b

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 00 b5

row#4[7911] flag: K-----, lock: 0, len=25

col 0; len 10; (10):  61 31 30 30 31 20 20 20 20 20

col 1; len 3; (3):  c2 0b 02

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 00 b5

row#5[7886] flag: K-----, lock: 0, len=25

col 0; len 10; (10):  61 31 30 30 32 20 20 20 20 20

col 1; len 3; (3):  c2 0b 03

tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

col  0: [ 4]  01 00 00 b5

DECLARE

   n   VARCHAR2 (2000);

BEGIN

   DBMS_STATS.convert_raw_value ('61', 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;

declare

   n number;

 begin

   dbms_stats.convert_raw_value('c102',

                                n);

   dbms_output.put_line(n);

 end; 

col 0; len 10; (10):  61 31 20 20 20 20 20 20 20 20  表示a1

col 1; len 2; (2):  c1 02 表示1

row#1[7984] flag: K-----, lock: 0, len=24

col 0; len 10; (10):  61 31 30 20 20 20 20 20 20 20  表示a10

col 1; len 2; (2):  c1 0b  表示10

row#2[7960] flag: K-----, lock: 0, len=24

col 0; len 10; (10):  61 31 30 30 20 20 20 20 20 20 表示a100

col 1; len 2; (2):  c2 02 --表示100

row#3[7936] flag: K-----, lock: 0, len=24

col 0; len 10; (10):  61 31 30 30 30 20 20 20 20 20 表示a1000

col 1; len 2; (2):  c2 0b  --表示1000

row#4[7911] flag: K-----, lock: 0, len=25

col 0; len 10; (10):  61 31 30 30 31 20 20 20 20 20 表示a10001

col 1; len 3; (3):  c2 0b 02 表示1001

row#5[7886] flag: K-----, lock: 0, len=25

col 0; len 10; (10):  61 31 30 30 32 20 20 20 20 20 表示a1002

col 1; len 3; (3):  c2 0b 03  表示1002

结论:secondary key 包含了主键对应的列 顺序为secondary key对应的列加上主键对应的列。

相关文章