Oracle索引分支块的结构

在这篇文章里,我们详细解析了Oracle索引分支块的结构。

我们还是从一个实例来开始说起:

SQL> create table t2 as select object_id,object_name from dba_objects;

 

Table created

 

SQL> select count(*) from t2;

 

  COUNT(*)

——————

  104530

 

SQL> create index idx_t2 on t2(object_name);

 

Index created

 

SQL> select object_id from dba_objects where object_name=’IDX_T2′;

 

 OBJECT_ID

——————–

  239443

   

SQL> oradebug setmypid

Statement processed.

 

SQL> alter session set events ‘immediate trace name treedump level 239443’;

 

Session altered.

 

SQL> oradebug tracefile_name

/nbstu01/app/oracle/diag/rdbms/nbstest/NBSTEST/trace/NBSTEST_ora_9699378.trc

 

 

/nbstu01/app/oracle/diag/rdbms/nbstest/NBSTEST/trace/NBSTEST_ora_9699378.trc的内容为如下所示:

oracle:/home/oracle>cat /nbstu01/app/oracle/diag/rdbms/nbstest/NBSTEST/trace/NBSTEST_ora_9699378.trc

Trace file /nbstu01/app/oracle/diag/rdbms/nbstest/NBSTEST/trace/NBSTEST_ora_9699378.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

……省略显示部分内容

 

*** 2012-12-28 13:00:47.047

Oradebug command ‘setmypid’ console output: <none>

 

*** 2012-12-28 13:01:11.575

—– begin tree dump

branch: 0x3800284 58720900 (0: nrow: 2, level: 2)

   branch: 0x38003c6 58721222 (-1: nrow: 316, level: 1)

      leaf: 0x3800285 58720901 (-1: nrow: 184 rrow: 184)

      ……省略显示部分内容     

      leaf: 0x38003c4 58721220 (314: nrow: 280 rrow: 280)

   branch: 0x3800461 58721377 (0: nrow: 153, level: 1)

      leaf: 0x38003c5 58721221 (-1: nrow: 218 rrow: 218)

      ……省略显示部分内容

      leaf: 0x3800460 58721376 (151: nrow: 46 rrow: 46)

—– end tree dump

 

*** 2012-12-28 13:01:21.828

Processing Oradebug command ‘tracefile_name’

 

*** 2012-12-28 13:01:21.828

Oradebug command ‘tracefile_name’ console output:

/nbstu01/app/oracle/diag/rdbms/nbstest/NBSTEST/trace/NBSTEST_ora_9699378.tr

 

从上述显示内容中我们可以看出,现在索引IDX_T2有如下这三个分支块:

branch: 0x3800284 58720900 (0: nrow: 2, level: 2)

branch: 0x38003c6 58721222 (-1: nrow: 316, level: 1)

branch: 0x3800461 58721377 (0: nrow: 153, level: 1)

 

SQL> set serveroutput on size 1000000

SQL> exec sys.cdba(‘03800284′,’H’);

 

.

The file is 14

The block is 644

 

PL/SQL procedure successfully completed

 

SQL> exec sys.cdba(‘038003c6′,’H’);

 

.

The file is 14

The block is 966

 

PL/SQL procedure successfully completed

 

SQL> select blevel from dba_indexes where index_name=’IDX_T2′;

 

    BLEVEL

——————–

      2

 

SQL> select header_file,header_block from dba_segments where owner=’NBSTEST’ and segment_name=’IDX_T2′;

 

HEADER_FILE HEADER_BLOCK

——————– ————————-

         14          643

        

我们现在直接来dump上述分分支块0x38003c6dump后的trace文件内容为如下所示:

Block header dump:  0x038003c6

 Object id on Block? Y

 seg/obj: 0x3a753  csc: 0x00.1986a526  itc: 1  flg: E  typ: 2 – INDEX

     brn: 0  bdba: 0x3800381 ver: 0x01 opc: 0

     inc: 0  exflg: 0

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.1986a526

Branch block dump

=================

header address 4575700044=0x110bba44c

kdxcolev 1

KDXCOLEV Flags = – – –

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 315

kdxcofbo 658=0x292

kdxcofeo 664=0x298

kdxcoavs 6

kdxbrlmc 58720901=0x3800285

kdxbrsno 0

kdxbrbksz 8056

kdxbr2urrc 0

row#0[8045] dba: 58720902=0x3800286

col 0; len 5; (5):  2f 31 31 31 62

col 1; TERM

row#1[8034] dba: 58720903=0x3800287

col 0; len 5; (5):  2f 31 32 36 61

col 1; TERM

 

……省略显示部分内容

 

row#9[7854] dba: 58720911=0x380028f

col 0; len 30; (30):

 2f 31 63 31 31 30 32 37 33 5f 41 57 45 78 63 65 70 74 69 6f 6e 4d 65 73 73

 61 67 65 52 65

col 1; len 4; (4):  03 80 01 b9

 

……省略显示部分内容

 

row#20[7528] dba: 58720922=0x380029a

col 0; len 30; (30):

 2f 32 61 35 33 35 36 61 32 5f 49 6e 6c 69 6e 65 52 65 74 75 72 6e 53 74 61

 74 65 6d 65 6e

col 1; len 6; (6):  03 80 00 d2 00 5b

 

……省略显示部分内容

 

row#230[2371] dba: 58721134=0x380036e

col 0; len 9; (9):  44 41 54 41 5f 54 52 4e 53

col 1; len 4; (4):  03 80 02 47

 

……省略显示部分内容

 

row#247[1970] dba: 58721151=0x380037f

col 0; len 10; (10):  44 4d 5f 43 4c 55 53 54 45 52

col 1; len 6; (6):  03 80 00 97 00 b8

 

……省略显示部分内容

 

 

先来看上述显示内容中的第9行记录:

row#9[7854] dba: 58720911=0x380028f

col 0; len 30; (30):

 2f 31 63 31 31 30 32 37 33 5f 41 57 45 78 63 65 70 74 69 6f 6e 4d 65 73 73

 61 67 65 52 65

col 1; len 4; (4):  03 80 01 b9

 

SQL> exec sys.undump(‘2f 31 63 31 31 30 32 37 33 5f 41 57 45 78 63 65 70 74 69 6f 6e 4d 65 73 73 61 67 65 52 65’);

 

/1c110273_AWExceptionMessageRe

 

 

PL/SQL procedure successfully completed

 

SQL> select count(*) from t2 where object_name=’/1c110273_AWExceptionMessageRe’;

 

  COUNT(*)

—————–

      2

 

SQL> select object_id,object_name,dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid)||’_’||dbms_rowid.rowid_row_number(rowid) location from t2 where object_name=’/1c110273_AWExceptionMessageRe’;

 

 OBJECT_ID OBJECT_NAME                                        LOCATION

—————– ————————————————– ————————————————————-

     64449 /1c110273_AWExceptionMessageRe                     14_440_169

     64560 /1c110273_AWExceptionMessageRe                     14_441_88

 

SQL> exec sys.cdba(‘038001b9′,’H’);

 

.

The file is 14

The block is 441

 

PL/SQL procedure successfully completed

 

上述第9行记录所在叶子块的起止地址为0x380028f

SQL> exec sys.cdba(‘0380028f’,’H’);

 

.

The file is 14

The block is 655

 

PL/SQL procedure successfully completed

 

上述叶子块的dump内容为如下所示:

Block header dump:  0x0380028f

 Object id on Block? Y

 seg/obj: 0x3a753  csc: 0x00.1986a526  itc: 2  flg: E  typ: 2 – INDEX

     brn: 0  bdba: 0x3800280 ver: 0x01 opc: 0

     inc: 0  exflg: 0

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

0x02   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.1986a526

Leaf block dump

===============

header address 4575700068=0x110bba464

kdxcolev 0

KDXCOLEV Flags = – – –

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 185

kdxcofbo 406=0x196

kdxcofeo 1244=0x4dc

kdxcoavs 838

kdxlespl 0

kdxlende 0

kdxlenxt 58720912=0x3800290

kdxleprv 58720910=0x380028e

kdxledsz 0

kdxlebksz 8032

row#0[7992] flag: ——, lock: 0, len=40

col 0; len 30; (30):

 2f 31 63 31 31 30 32 37 33 5f 41 57 45 78 63 65 70 74 69 6f 6e 4d 65 73 73

 61 67 65 52 65

col 1; len 6; (6):  03 80 01 b9 00 58

 

 

然后我们再来dump上述叶子块的前一个块(即kdxleprv 58720910=0x380028e);

row#183[1250] flag: ——, lock: 0, len=40

col 0; len 30; (30):

 2f 31 63 31 31 30 32 37 33 5f 41 57 45 78 63 65 70 74 69 6f 6e 4d 65 73 73

 61 67 65 52 65

col 1; len 6; (6):  03 80 01 b8 00 a9

—– end of leaf block dump —–

End dump data blocks tsn: 22 file#: 14 minblk 654 maxblk 654

 

 

也就是说上述第9行记录对应了两个索引行,这两个索引行恰好分布在两个叶子块中,一个在叶子块0x0380028f中,一个在叶子块0x380028e

 

 

再来看上述分支块的dump内容中的第20行记录:

row#20[7528] dba: 58720922=0x380029a

col 0; len 30; (30):

 2f 32 61 35 33 35 36 61 32 5f 49 6e 6c 69 6e 65 52 65 74 75 72 6e 53 74 61

 74 65 6d 65 6e

col 1; len 6; (6):  03 80 00 d2 00 5b

 

SQL> exec sys.undump(‘2f 32 61 35 33 35 36 61 32 5f 49 6e 6c 69 6e 65 52 65 74 75 72 6e 53 74 61 74 65 6d 65 6e’);

 

/2a5356a2_InlineReturnStatemen

 

 

PL/SQL procedure successfully completed

 

SQL> select count(*) from t2 where object_name=’/2a5356a2_InlineReturnStatemen’;

 

  COUNT(*)

———-

      2

 

SQL> select object_id,object_name,dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid)||’_’||dbms_rowid.rowid_row_number(rowid) location from t2 where object_name=’/2a5356a2_InlineReturnStatemen’;

 

 OBJECT_ID OBJECT_NAME                                  LOCATION

—————– ————————————————– ————————————————-

     18887 /2a5356a2_InlineReturnStatemen                     14_210_90

     18888 /2a5356a2_InlineReturnStatemen                     14_210_91

 

SQL> exec sys.cdba(‘038000d2′,’H’);

 

.

The file is 14

The block is 210

 

PL/SQL procedure successfully completed

 

上述第20行记录所在叶子块的起始地址为0x380029a

SQL> exec sys.cdba(‘0380029a’,’H’);

 

.

The file is 14

The block is 666

 

PL/SQL procedure successfully completed

 

上述叶子块的dump内容为如下所示:

Block header dump:  0x0380029a

 Object id on Block? Y

 seg/obj: 0x3a753  csc: 0x00.1986a526  itc: 2  flg: E  typ: 2 – INDEX

     brn: 0  bdba: 0x3800280 ver: 0x01 opc: 0

     inc: 0  exflg: 0

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

0x02   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.1986a526

Leaf block dump

===============

header address 4575700068=0x110bba464

kdxcolev 0

KDXCOLEV Flags = – – –

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 180

kdxcofbo 396=0x18c

kdxcofeo 1242=0x4da

kdxcoavs 846

kdxlespl 0

kdxlende 0

kdxlenxt 58720923=0x380029b

kdxleprv 58720921=0x3800299

kdxledsz 0

kdxlebksz 8032

row#0[7992] flag: ——, lock: 0, len=40

col 0; len 30; (30):

 2f 32 61 35 33 35 36 61 32 5f 49 6e 6c 69 6e 65 52 65 74 75 72 6e 53 74 61

 74 65 6d 65 6e

col 1; len 6; (6):  03 80 00 d2 00 5b

 

然后我们再来dump上述叶子块的前一个块(即58720921=0x3800299);

row#187[1229] flag: ——, lock: 0, len=40

col 0; len 30; (30):

 2f 32 61 35 33 35 36 61 32 5f 49 6e 6c 69 6e 65 52 65 74 75 72 6e 53 74 61

 74 65 6d 65 6e

col 1; len 6; (6):  03 80 00 d2 00 5a

—– end of leaf block dump —–

End dump data blocks tsn: 22 file#: 14 minblk 665 maxblk 665

 

 

也就是说上述第20行记录对应了两个索引行,这两个索引行也恰好分布在两个叶子块中,一个在叶子块0x0380029a中,一个在叶子块0x03800299

 

 

再来看上述分支块dump内容中的第230行记录:

因分析过程和结论和上述类似,这里略去。

 

从上述测试过程中我们可以得出如下结论:

1、每个索引分支块都只有一个lmc,这个lmc指向的分支块/叶子块中的所有索引键值列中的最大值一定小于该lmc所在分支块的所有索引键值列中的最小值;

2索引分支块的行记录所对应的存储格式为“行头 + 分支块/叶子块的RDBA + col 0 + col 1

   (2a) 上述存储格式中的col 0为索引键值列,注意,col 0可能为索引键值列的缩写;

   (2b) col 0为完整的索引键值列时,col 1等于该行行头“分支块/叶子块的RDBA”所指向的叶子块中的第一行索引行所对应的数据行的ROWID(或ROWID的前缀):如果上述叶子块的第一行索引行所对应的数据行的ROWID和该叶子块kdxleprv所指向的叶子块的最后一行索引行所对应的数据行的ROWID不同,则Oracle只需记录上述叶子块的第一行索引行所对应的数据行的ROWIDROWID的前缀,只要能和kdxleprv所指向的叶子块的最后一行索引行所对应的数据行的ROWID区分开就行,可以不是完整的ROWID)就可以了;

   (2c) col 0为索引键值列的缩写时,col 1等于一个固定值“TERM;

   (2d) 上述存储格式中的“分支块/叶子块的RDBA”指向的分支块/叶子块中的所有索引键值列中的最小值一定大于等于该行记录中col 0的值,但这并不意味着所有索引键值列等于该行记录中col 0的索引行都分布在该行行头的“分支块/叶子块的RDBA”中。

 

 

我们来看col1记录的值为其ROWID3byte的实例:

row#53[6424] dba: 184552507=0xb000c3b

col 0; len 19; (19):  2f 33 32 34 33 37 66 62 5f 43 61 63 68 65 54 61 62 6c 65

col 1; len 3; (3):  01 43 d1

 

SQL> exec sys.undump(‘2f 33 32 34 33 37 66 62 5f 43 61 63 68 65 54 61 62 6c 65’);

 

/32437fb_CacheTable

 

SQL> select object_id,object_name,dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid)||’_’||dbms_rowid.rowid_row_number(rowid) location from t2 where object_name=’/32437fb_CacheTable’;

 

 OBJECT_ID OBJECT_NAME                  LOCATION

———- ——————————————————– —————————————————–

     31012 /32437fb_CacheTable               5_249873_144

     31013 /32437fb_CacheTable               5_249873_145

     31012 /32437fb_CacheTable               5_250365_42

     31013 /32437fb_CacheTable               5_250365_43

 

上述分支块的行记录所对应叶子块的dump内容为如下所示:

Start dump data blocks tsn: 13 file#:44 minblk 3131 maxblk 3131

Block dump from cache:

Dump of buffer cache at level 4 for tsn=13 rdba=184552507

BH (0x70000006efd8988) file#: 44 rdba: 0x0b000c3b (44/3131) class: 1 ba: 0x70000006ebdc000

……省略显示部分内容

Block header dump:  0x0b000c3b

 Object id on Block? Y

 seg/obj: 0x41a19  csc: 0x00.38dd6a43  itc: 2  flg: E  typ: 2 – INDEX

     brn: 0  bdba: 0xb000c00 ver: 0x01 opc: 0

     inc: 0  exflg: 0

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

0x02   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.38dd6a43

Leaf block dump

===============

header address 4575700068=0x110bba464

kdxcolev 0

KDXCOLEV Flags = – – –

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 186

kdxcofbo 408=0x198

kdxcofeo 1254=0x4e6

kdxcoavs 846

kdxlespl 0

kdxlende 0

kdxlenxt 184552508=0xb000c3c

kdxleprv 184552506=0xb000c3a

kdxledsz 0

kdxlebksz 8032

row#0[8003] flag: ——, lock: 0, len=29

col 0; len 19; (19):  2f 33 32 34 33 37 66 62 5f 43 61 63 68 65 54 61 62 6c 65

col 1; len 6; (6):  01 43 d1 fd 00 2a

row#1[7974] flag: ——, lock: 0, len=29

col 0; len 19; (19):  2f 33 32 34 33 37 66 62 5f 43 61 63 68 65 54 61 62 6c 65

col 1; len 6; (6):  01 43 d1 fd 00 2b

 

再来dump上述叶子块的前一个块:

Start dump data blocks tsn: 13 file#:44 minblk 3130 maxblk 3130

Block dump from cache:

Dump of buffer cache at level 4 for tsn=13 rdba=184552506

BH (0x700000066f9ac58) file#: 44 rdba: 0x0b000c3a (44/3130) class: 1 ba: 0x70000006655a000

……省略显示部分内容

row#179[1261] flag: ——, lock: 0, len=29

col 0; len 19; (19):  2f 33 32 34 33 37 66 62 5f 43 61 63 68 65 54 61 62 6c 65

col 1; len 6; (6):  01 43 d0 11 00 90

row#180[1232] flag: ——, lock: 0, len=29

col 0; len 19; (19):  2f 33 32 34 33 37 66 62 5f 43 61 63 68 65 54 61 62 6c 65

col 1; len 6; (6):  01 43 d0 11 00 91

—– end of leaf block dump —–

End dump data blocks tsn: 13 file#: 44 minblk 3130 maxblk 3130

 

这里上述叶子块的第一行记录所对应的ROWID01 43 d1 fd 00 2b,其前一个叶子块的最后一行记录所对应的ROWID01 43 d0 11 00 91,这两个ROWID的头3byte分别为01 43 d101 43 d0,已然不同,所以上述分支块的行记录的col1只用记录01 43 d1就可以了。

 

最后来看一个col1记录的值为其ROWID1byte的实例:

row#18[7464] dba: 184552472=0xb000c18

col 0; len 20; (20):  2f 31 62 39 34 37 31 65 38 5f 53 69 67 6e 61 74 75 72 65 31

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

 

上述分支块的行记录所对应叶子块的dump内容为如下所示:

Start dump data blocks tsn: 13 file#:44 minblk 3096 maxblk 3096

Block dump from cache:

Dump of buffer cache at level 4 for tsn=13 rdba=184552472

BH (0x700000069fbc778) file#: 44 rdba: 0x0b000c18 (44/3096) class: 1 ba: 0x7000000698e6000

…省略显示部分内容

Block header dump:  0x0b000c18

 Object id on Block? Y

 seg/obj: 0x41a19  csc: 0x00.38dd6a43  itc: 2  flg: E  typ: 2 – INDEX

     brn: 0  bdba: 0xb000c00 ver: 0x01 opc: 0

     inc: 0  exflg: 0

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000

0x02   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.38dd6a43

Leaf block dump

===============

header address 4575700068=0x110bba464

kdxcolev 0

KDXCOLEV Flags = – – –

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 182

kdxcofbo 400=0x190

kdxcofeo 1224=0x4c8

kdxcoavs 824

kdxlespl 0

kdxlende 0

kdxlenxt 184552473=0xb000c19

kdxleprv 184552471=0xb000c17

kdxledsz 0

kdxlebksz 8032

row#0[8002] flag: ——, lock: 0, len=30

col 0; len 20; (20):  2f 31 62 39 34 37 31 65 38 5f 53 69 67 6e 61 74 75 72 65 31

col 1; len 6; (6):  0b 00 0b 6a 00 57

row#1[7972] flag: ——, lock: 0, len=30

col 0; len 20; (20):  2f 31 62 39 34 37 31 65 38 5f 53 69 67 6e 61 74 75 72 65 31

col 1; len 6; (6):  0b 00 0b 6a 00 58

 

再来dump上述叶子块的前一个块:

Start dump data blocks tsn: 13 file#:44 minblk 3095 maxblk 3095

Block dump from cache:

Dump of buffer cache at level 4 for tsn=13 rdba=184552471

BH (0x700000059f696a8) file#: 44 rdba: 0x0b000c17 (44/3095) class: 1 ba: 0x700000059028000

……省略显示部分内容

row#185[1266] flag: ——, lock: 0, len=30

col 0; len 20; (20):  2f 31 62 39 34 37 31 65 38 5f 53 69 67 6e 61 74 75 72 65 31

col 1; len 6; (6):  01 43 d1 a7 00 05

row#186[1236] flag: ——, lock: 0, len=30

col 0; len 20; (20):  2f 31 62 39 34 37 31 65 38 5f 53 69 67 6e 61 74 75 72 65 31

col 1; len 6; (6):  01 43 d1 a7 00 06

—– end of leaf block dump —–

End dump data blocks tsn: 13 file#: 44 minblk 3095 maxblk 3095

 

这里上述叶子块的第一行记录所对应的ROWID0b 00 0b 6a 00 57,其前一个叶子块的最后一行记录所对应的ROWID01 43 d1 a7 00 06,这两个ROWID的头1byte分别为0b01,已然不同,所以上述分支块的行记录的col1只用记录0b就可以了。


12 Comments on “Oracle索引分支块的结构”

  1. songwei says:

    Hi,
    sys.undump存储过程的源码可否提供下?

    • Cui Hua says:

      create or replace procedure undump
      (i_vc_input in varchar2) is
      /*
      功能: 将dump出来的16进制文本内容翻译成其原始文本,目前仅支持ZHS16GBK和AL32UTF8字符集
      作者: dbsnake
      创建日期:2010-11-30

      输入参数:
      i_vc_input: 输入的dump出来的16进制文本内容

      输出参数:

      输入输出参数:

      调用到的存储过程:

      */

      o_vc_return_flag varchar2(4000);
      i_vc_input_compress varchar2(4000);
      vc_characterset varchar2(4000);

      type type_character_number is table of number index by binary_integer;
      characters_number type_character_number;

      j number;
      n_temp number;
      n_skipflag number;

      begin

      if( instr(i_vc_input,’,’) > 0 ) then
      i_vc_input_compress := trim(replace(i_vc_input,’,’,”));
      elsif ( instr(i_vc_input,’ ‘) > 0 ) then
      i_vc_input_compress := trim(replace(i_vc_input,’ ‘,”));
      else
      i_vc_input_compress := trim(i_vc_input);
      end if;

      select value into vc_characterset from nls_database_parameters where parameter=’NLS_CHARACTERSET’;

      j := 1;
      n_skipflag := 0;
      for i in 1 .. length(i_vc_input_compress) loop

      if ( n_skipflag > 0 ) then
      n_skipflag := n_skipflag – 1;
      end if;

      if ( n_skipflag = 0 ) then
      select to_number(substr(i_vc_input_compress,i,2),’XXXXXXXXXXXX’) into n_temp from dual;

      if (n_temp < 128 ) then characters_number(j) := n_temp; j := j + 1; n_skipflag := 2; else if ( vc_characterset = 'ZHS16GBK' ) then select to_number(substr(i_vc_input_compress,i,4),'XXXXXXXXXXXX') into n_temp from dual; characters_number(j) := n_temp; j := j + 1; n_skipflag := 4; elsif( vc_characterset = 'AL32UTF8' ) then select to_number(substr(i_vc_input_compress,i,6),'XXXXXXXXXXXX') into n_temp from dual; characters_number(j) := n_temp; j := j + 1; n_skipflag := 6; else select to_number(substr(i_vc_input_compress,i,4),'XXXXXXXXXXXX') into n_temp from dual; characters_number(j) := n_temp; j := j + 1; n_skipflag := 4; end if; end if; end if; end loop; if ( characters_number.count > 0 ) then
      for k in characters_number.first .. characters_number.last loop
      if( characters_number(k) > 31 ) then
      dbms_output.put(chr(characters_number(k)));
      end if;
      end loop;
      end if;

      dbms_output.put_line(chr(10));

      exception
      when others then
      o_vc_return_flag := ‘E’ || ‘_’ || sqlcode || ‘_’ || sqlerrm;
      dbms_output.put_line(o_vc_return_flag);
      return;

      end undump;
      /

      • songwei says:

        谢谢,很喜欢你的书

      • songwei says:

        创建了undump存储过程,但是测试过程中如下:
        SQL> exec sys.undump(‘2f 31 31 66 32 37 39 35 61 5f 44 54 44 47 72 61 6d 6d 61 72 43 68 69 6c 64 72 65 6e 4c 69’);

        PL/SQL procedure successfully completed.

        并没有将dump来的数据翻译成原始文本

        • Cui Hua says:

          需要先执行set serveroutput on

          • songwei says:

            关于oracle自定义外连接”(+)”
            书中写的关于full outer join的等价改写:
            SQL> select t1.col1,t1.col2,t2.col3
            2 from t1,t2
            3 where t1.col2=t2.col2(+)
            4 union
            5 select t1.col1,t1.col2,t2.col3
            6 from t1,t2
            7 where t1.col2(+)=t2.col2;
            您提到Oracle断然不会采用这样的等价改写,而是通过如下的改写:
            SQL> select t1.col1,t1.col2,t2.col3
            2 from t1,t2
            3 where t1.col2=t2.col2(+)
            4 union all
            5 select null,null,t2.col3
            6 from t2
            7 where not exists ( select 1 from t1 where t1.col2=t2.col2);
            通过这两种改写方式得到的结果是一致的,并查看了两种改写的执行计划,只是在第一种方式中多了union的排序操作,是因为这个原因吗?

      • songwei says:

        已解决

  2. 张小宝 says:

    sys.cdba()不是系统自带的?

    • Cui Hua says:

      不是,CDBA的源码如下:
      CREATE OR REPLACE PROCEDURE cdba ( iblock VARCHAR2, imode VARCHAR2 ) AS

      x NUMBER;
      digits# NUMBER;
      results NUMBER := 0;
      file# NUMBER := 0;
      block# NUMBER := 0;
      cur_digit CHAR(1);
      cur_digit# NUMBER;

      BEGIN
      IF upper(imode) = ‘H’ THEN
      digits# := length( iblock );
      FOR x IN 1..digits# LOOP
      cur_digit := upper(substr( iblock, x, 1 ));
      IF cur_digit IN (‘A’,’B’,’C’,’D’,’E’,’F’) THEN
      cur_digit# := ascii( cur_digit ) – ascii(‘A’) +10;
      ELSE
      cur_digit# := to_number(cur_digit);
      END IF;
      results := (results *16) + cur_digit#;
      END LOOP;
      ELSE
      IF upper(imode) = ‘D’ THEN
      –results := to_number(iblock,’XXXXXXXX’);
      results := iblock;
      ELSE
      dbms_output.put_line(‘H = Hex Input … D = Decimal Input’);
      RETURN;
      END IF;
      END IF;

      file# := dbms_utility.data_block_address_file(results);
      block# := dbms_utility.data_block_address_block(results);

      dbms_output.put_line(‘.’);
      dbms_output.put_line( ‘The file is ‘ || file# );
      dbms_output.put_line( ‘The block is ‘ || block# );
      END;
      /

  3. Cui Hua says:

    Reply to songwei: 排序只是union的副作用,这里为什么用union来模拟是因为我需要union所带来的“去重”

    • songwei says:

      Hi,

      看了数中关于Session Cursor部分,您在书中提到“对于Oracle 11g之前的版本,在缓存Session Cursor的哈希表对应Hash Bucket中,Oracle会存储目标SQL对应的Parent Cursor的库缓存对象句柄地址”

      我的数据库是11gR2,查看session cursor dump

      ———————————————————–
      ————– Generic Session Cached Cursors Dump ——–
      ———————————————————–
      hash table=0xb7dc268c cnt=34 LRU=0xb7db7420 cnt=34 hit=133 max=50 NumberOfTypes=6
      type#0 name=DICTION count=0
      type#1 name=BUNDLE count=14
      type#2 name=SESSION count=19
      type#3 name=PL/SQL count=1
      type#4 name=CONSTRA count=0
      type#5 name=REPLICA count=0
      Bucket#001 seg=0xb7dc26a4 nit=5 nal=5 ips=5 sz=32 flg=3 ucnt=1
      0 cob=0xb7d0671c idx=1 flg=0 typ=2 cur=0xb7d067c8 lru=1 fl=101
      Bucket#002 seg=0xb7dc26c4 nit=5 nal=5 ips=5 sz=32 flg=3 ucnt=1
      0 cob=0xb7dca704 idx=2 flg=0 typ=1 cur=0xb7dca7b0 lru=1 fl=15
      Bucket#004 seg=0xb7dc2704 nit=5 nal=5 ips=5 sz=32 flg=3 ucnt=1
      0 cob=0xb7d21260 idx=4 flg=0 typ=3 cur=0xb7d2130c lru=1 fl=15
      …….

      发现没有hdl列用于指向Parent Cursor

      想问下Oracle 11g开始Session Cursor是怎么实现的?

      • Cui Hua says:

        Oracle 11g的session cursor的dump确实没有hdl列,但至于为什么没有,我不清楚。可能是因为11g的session cursor dump不显示hdl列或者是Oracle在11g中更改了session cursor的机制。


Leave a Reply

Your email address will not be published. Required fields are marked *