X$MESSAGES hidden columns

Sometimes X$ tables might have seemingly unused gaps in a row. Here is an example with X$MESSAGES:

SQL> select kqftarsz
  2    from x$kqfta
  3   where kqftanam = 'X$MESSAGES'
  4  /

  KQFTARSZ
----------
        32

SQL> select c.kqfconam column_name,
  2         c.kqfcodty datatype,
  3         c.kqfcosiz size_byte,
  4         c.kqfcooff offset
  5    from x$kqfta t,
  6         x$kqfco c
  7   where t.kqftanam = 'X$MESSAGES'
  8     and c.kqfcotab = t.indx
  9   order by c.indx
 10  /

COLUMN_NAME   DATATYPE  SIZE_BYTE     OFFSET
----------- ---------- ---------- ----------
ADDR                23          8          0
INDX                 2          4          0
INST_ID              2          4          0
CON_ID               2          2          0
DESCRIPTION          1         64          8
DEST                 1         64         16

6 rows selected.

SQL> desc x$messages
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_ID                                             NUMBER
 DESCRIPTION                                        VARCHAR2(64)
 DEST                                               VARCHAR2(64)

Having looked carefully at the output above, several discrepancies can be noticed:

• How come the row size is 32 bytes whereas there are only two non-generated columns of 8 bytes each (DESCRIPTION and DEST, which are pointers)?
• If the row size is 32 bytes, and DESCRIPTION and DEST starts at offsets 8 and 16 correspondingly, what data is in bytes 0-7 and 24-31?

Based on the fact that X$MESSAGES takes its data from ksbsdt (see X$ tables: starting address), the structure itself can be inspected in case it can help connect the dots.

[oracle@rac1 bin]$ readelf -s oracle | grep -w ksbsdt
203009: 00000000152e5760 16992 OBJECT  GLOBAL DEFAULT   17 ksbsdt
218757: 00000000152e5760 16992 OBJECT  GLOBAL DEFAULT   17 ksbsdt
[oracle@rac1 bin]$ objdump -s --start-address=0x00000000152e5760 --stop-address=$((0x00000000152e5760+16992)) oracle

oracle:     file format elf64-x86-64

Contents of section .rodata:
 152e5760 203eee00 00000000 48f12e15 00000000   >......H.......
 152e5770 f818be13 00000000 11000000 00000000  ................
 152e5780 d04aee00 00000000 60f12e15 00000000  .J......`.......
 152e5790 f818be13 00000000 01000000 00000000  ................
 152e57a0 f0b11307 00000000 78f12e15 00000000  ........x.......
 152e57b0 f818be13 00000000 00000000 00000000  ................
 152e57c0 80043401 00000000 90f12e15 00000000  ..4.............
 152e57d0 f818be13 00000000 05000000 00000000  ................
 152e57e0 f0162707 00000000 acf12e15 00000000  ..'.............
 152e57f0 c0b28f15 00000000 00000000 00000000  ................
...

It can be seen that there is a symbol at byte 0 and some number at byte 24; byte 8 and byte 16 has expected pointers:

[oracle@rac1 bin]$ readelf -s oracle | grep 00ee3e20
 30104: 0000000000ee3e20  1344 FUNC    GLOBAL DEFAULT   13 ksl_pdb_event_stats_exten
322705: 0000000000ee3e20  1344 FUNC    GLOBAL DEFAULT   13 ksl_pdb_event_stats_exten
[oracle@rac1 bin]$ objdump -s --start-address=0x152ef148 --stop-address=$((0x152ef148+32)) oracle

oracle:     file format elf64-x86-64

Contents of section .rodata:
 152ef148 70646220 6576656e 74207374 61747320  pdb event stats
 152ef158 61637469 6f6e0000 57616974 204f7574  action..Wait Out
[oracle@rac1 bin]$ objdump -s --start-address=0x13be18f8 --stop-address=$((0x13be18f8+32)) oracle

oracle:     file format elf64-x86-64

Contents of section .rodata:
 13be18f8 47454e30 00000000 4c4d4643 00000000  GEN0....LMFC....
 13be1908 44425730 00000000 4c434b31 00000000  DBW0....LCK1....
...

Thus, the missing columns can be obtained using another tool that I wrote for such cases - bide (binary data extractor):

[oracle@rac1 src]$ bide dump-table ksbsdt --format func:symbol description:string dest:string L
+----------------------------------------+-----------------------------------------------------------------+------------------------------------+-----------+
| func                                   | description                                                     | dest                               | _unnamed3 |
+----------------------------------------+-----------------------------------------------------------------+------------------------------------+-----------+
| ksl_pdb_event_stats_extend             | pdb event stats action                                          | GEN0                               |        17 |
| kslwo_compute_sys_thresholds_bg_action | Wait Outlier thresholds                                         | GEN0                               |         1 |
| kslwo_process_sys_wait_bg_action       | Process wait outlier                                            | GEN0                               |         0 |
| ksuguid_mac_update_act                 | KSU GUID MAC Address update                                     | GEN0                               |         5 |
| ksu_inc_sos_param                      | session param update action                                     | GEN1                               |         0 |
| ksu_inc_sos                            | session update action                                           | GEN2                               |         1 |
| kstmUpdateTranslationCbk               | Update KGSTM Translation                                        | MMON                               |         1 |
| ksustaen_init_act                      | event nfy timeout action                                        | GEN0                               |         8 |
| ksbmsg                                 | KSB action for X-instance calls                                 | *                                  |         2 |
| ksbckbast                              | KSB action for bast checking                                    | *                                  |         1 |
| ksbsht                                 | generic shutdown background                                     | *                                  |         0 |
...

Comparing it with the data seen in the database, it can be concluded that not only do we have a description of each "message" and a process presumably handling it, but also we have an underlying function:

SQL> select * from x$messages;

ADDR                   INDX    INST_ID     CON_ID DESCRIPTION                                                      DEST
---------------- ---------- ---------- ---------- ---------------------------------------------------------------- -----
00000000152E5760          0          1          0 pdb event stats action                                           GEN0
00000000152E5780          1          1          0 Wait Outlier thresholds                                          GEN0
00000000152E57A0          2          1          0 Process wait outlier                                             GEN0
00000000152E57C0          3          1          0 KSU GUID MAC Address update                                      GEN0
00000000152E57E0          4          1          0 session param update action                                      GEN1
00000000152E5800          5          1          0 session update action                                            GEN2
00000000152E5820          6          1          0 Update KGSTM Translation                                         MMON
00000000152E5840          7          1          0 event nfy timeout action                                         GEN0
00000000152E5860          8          1          0 KSB action for X-instance calls                                  *
00000000152E5880          9          1          0 KSB action for bast checking                                     *
00000000152E58A0         10          1          0 generic shutdown background                                      *
...

This allows one to execute those actions by calling the respective function. It also explains what is stored in bytes 0-7 and 24-31 of X$MESSAGES rows.

X$ tables: starting address

The starting address of X$ tables makes sense for tables residing in SGA or defined within the Oracle binary. It is usually found by querying an X$ table. This blog post describes an alternative method of obtaining the starting address by calling an Oracle C function.

The Oracle function that can be used here is qerfxArrayMaxSize. Here is a short GDB script demonstrating its usage:

set pagination off
set trace-commands on

set $ctx = (int *)malloc(4)

def x_desc
  printf "result = 0x%x\n", (int)qerfxArrayMaxSize($arg0, (int)strlen($arg0), (int *)$ctx)
  printf "start_addr = 0x%x\n", *(long *)($ctx+18)
  info symbol *(int *)($ctx+18)
  p/a (long[10])(**(long *)($ctx+18))
  printf "row_count = %d\n", *(int *)($ctx+20)
end

x_desc "X$KSUPR"
x_desc "X$KSUSE"
x_desc "X$MESSAGES"

The three tables are going to be examined in separate sections below: X$KSUPR, X$KSUSE, X$MESSAGES.

X$KSUPR

The GDB output:

+x_desc "X$KSUPR"
++printf "result = 0x%x\n", (int)qerfxArrayMaxSize("X$KSUPR", (int)strlen("X$KSUPR"), (int *)$ctx)
result = 0x9a537260
++printf "start_addr = 0x%x\n", *(long *)($ctx+18)
start_addr = 0x9a537260
++info symbol *(int *)($ctx+18)
No symbol matches *(int *)($ctx+18).
++p/a (long[10])(**(long *)($ctx+18))
$1 = {0x9a588070, 0x9a5895e0, 0x9a58ab50, 0x9a58c0c0, 0x9a58d630, 0x9a58eba0, 0x9a590110, 0x9a591680, 0x9a592bf0, 0x9a594160}
++printf "row_count = %d\n", *(int *)($ctx+20)
row_count = 600

The SQL*Plus output:

SQL> select addr from x$ksupr where rownum<=10;

ADDR
----------------
000000009A588070
000000009A5895E0
000000009A58AB50
000000009A58C0C0
000000009A58D630
000000009A58EBA0
000000009A590110
000000009A591680
000000009A592BF0
000000009A594160

10 rows selected.

Thus, the result of the function is a pointer to an array storing pointers to X$KSUPR rows (0x9a588070, 0x9a5895e0, 0x9a58ab50, etc.). The function also conveniently returns the number of rows of the relevant fixed array:

++printf "row_count = %d\n", *(int *)($ctx+20)
row_count = 600

SQL> select count(*) from x$ksupr;

  COUNT(*)
----------
       600

SQL> select value from v$parameter where name='processes';

VALUE
--------------------------------------------------------------------------------
600

X$KSUSE

The GDB output:

+x_desc "X$KSUSE"
++printf "result = 0x%x\n", (int)qerfxArrayMaxSize("X$KSUSE", (int)strlen("X$KSUSE"), (int *)$ctx)
result = 0x39c
++printf "start_addr = 0x%x\n", *(long *)($ctx+18)
start_addr = 0x9ae5a808
++info symbol *(int *)($ctx+18)
No symbol matches *(int *)($ctx+18).
++p/a (long[10])(**(long *)($ctx+18))
$2 = {0x9a8ec740, 0x9a8eef28, 0x9a8f1710, 0x9a8f3ef8, 0x9a8f66e0, 0x9a8f8ec8, 0x9a8fb6b0, 0x9a8fde98, 0x9a900680, 0x9a902e68}
++printf "row_count = %d\n", *(int *)($ctx+20)
row_count = 924

This time around the function returns the number of rows (0x39c=924) of the corresponding array.

The SQL*Plus output:

SQL> select addr from x$ksuse where rownum<=10;

ADDR
----------------
000000009A8EC740
000000009A8EEF28
000000009A8F1710
000000009A8F3EF8
000000009A8F66E0
000000009A8F8EC8
000000009A8FB6B0
000000009A8FDE98
000000009A900680
000000009A902E68

10 rows selected.

SQL> select count(*) from x$ksuse;

  COUNT(*)
----------
       924

SQL> select value from v$parameter where name='sessions';

VALUE
--------------------------------------------------------------------------------
924

X$MESSAGES

The GDB output:

+x_desc "X$MESSAGES"
++printf "result = 0x%x\n", (int)qerfxArrayMaxSize("X$MESSAGES", (int)strlen("X$MESSAGES"), (int *)$ctx)
result = 0x212
++printf "start_addr = 0x%x\n", *(long *)($ctx+18)
start_addr = 0x152e5760
++info symbol *(int *)($ctx+18)
ksbsdt in section .rodata of /u01/app/oracle/product/19.3.0/dbhome_1/bin/oracle
++p/a (long[10])(**(long *)($ctx+18))
$3 = {0xee3e20 <ksl_pdb_event_stats_extend>, 0x152ef148, 0x13be18f8, 0x11, 0xee4ad0 <kslwo_compute_sys_thresholds_bg_action>, 0x152ef160, 0x13be18f8, 0x1, 0x713b1f0 <kslwo_process_sys_wait_bg_action>, 0x152ef178}
++printf "row_count = %d\n", *(int *)($ctx+20)
row_count = 530

The output above is very different from both X$KSUPR and X$KSUSE - the starting address is the actual address of the ksbsdt structure defined in the Oracle binary:

[oracle@rac1 bin]$ readelf -s oracle | grep 152e5760
203009: 00000000152e5760 16992 OBJECT  GLOBAL DEFAULT   17 ksbsdt
218757: 00000000152e5760 16992 OBJECT  GLOBAL DEFAULT   17 ksbsdt

The SQL*Plus output:

SQL> select addr from x$messages where rownum<=10;

ADDR
----------------
00000000152E5760
00000000152E5780
00000000152E57A0
00000000152E57C0
00000000152E57E0
00000000152E5800
00000000152E5820
00000000152E5840
00000000152E5860
00000000152E5880

10 rows selected.

SQL> select count(*) from x$messages;

  COUNT(*)
----------
       530

Conclusion

The qerfxArrayMaxSize function can be used to determine the starting address of X$ tables residing in SGA or Oracle binary. The return value of the function is not very consistent: the function returned the starting address for X$KSUPR and the number of rows for X$KSUSE/X$MESSAGES. It might be the case that the function returns the void type and the results are inconsistent because we are just examining $rax. By contrast, the memory area defined by the third parameter always stores the starting address and the number of rows for the tables I tested it with. I consider it is reliable for these types of tables (non-UGA/non-PGA based). I am disposed to think that X$KQFTA.KQFTATYP and maybe KQFTAFLG determine whether it is an SGA/PGA/UGA/Oracle binary based table:

SQL> select kqftanam, kqftatyp, kqftaflg
  2    from x$kqfta
  3   where kqftanam in ('X$KSUPR', 'X$KSUSE', 'X$MESSAGES')
  4  /

KQFTANAM                         KQFTATYP   KQFTAFLG
------------------------------ ---------- ----------
X$KSUSE                                 2          1
X$KSUPR                                 2          0
X$MESSAGES                              1          0

However, more experiments need to be conducted to confirm that. I initially started looking at this because I found that I could not determine the static structure behind X$MESSAGES using my xinfo tool:

[oracle@rac1 ~]$ xinfo list 'X$MESSAGES' --with-kqftap
+------------+-----+------------+------------+-----------------+---------+-----+-----+-----+-----+-----------------------------------------------------+
|        obj | ver |    nam_ptr | nam        | xstruct_nam_ptr | xstruct | typ | flg | rsz | coc | kqftap                                              |
+------------+-----+------------+------------+-----------------+---------+-----+-----+-----+-----+-----------------------------------------------------+
| 4294950992 |   3 | 0x14b845e0 | X$MESSAGES |      0x14b845ec | ksbsd   |   1 |   0 |  32 |   6 | {'xstruct_ptr': '0x14a26180', 'xstruct': 'ksbsd_c'} |
+------------+-----+------------+------------+-----------------+---------+-----+-----+-----+-----+-----------------------------------------------------+

There are structures such as ksbsd/ksbsd_c but there is no explicit ksbsdt. qerfxArrayMaxSize can be used to identify this missing structure.

Computing ORA_HASH

I wrote a short Python function to compute a hash value for a given expression. The return value of the function matches the return value of ORA_HASH on inputs I tested it with in Oracle 23c on Linux x86-64.

In its simplest form, the invocation can be as follows:

>>> ora_hash(b'test')
2662839991

SQL> select ora_hash('test');

ORA_HASH('TEST')
----------------
      2662839991

Handling non-string datatypes

In case of a non-string datatype, some conversion should be performed. ORA_HASH accepts a variety of datatypes. I use NUMBER and DATE in the examples below. The procedure is essentially the same with both datatypes: need to take the internal representation of a value in Oracle and pass it to Python. It should be possible to handle any other supported datatypes following the same procedure.

NUMBER

SQL> select ora_hash(2023);

ORA_HASH(2023)
--------------
    2671887358

SQL> select dump(2023, 16);

DUMP(2023,16)
---------------------
Typ=2 Len=3: c2,15,18

>>> ora_hash(b'\xc2\x15\x18')
2671887358

DATE

SQL> select ora_hash(to_date('2023-03-01 12:34:56', 'yyyy-mm-dd hh24:mi:ss'));

ORA_HASH(TO_DATE('2023-03-0112:34:56','YYYY-MM-DDHH24:MI:SS'))
--------------------------------------------------------------
                                                     112410422
                                                     
SQL> select dump(to_date('2023-03-01 12:34:56', 'yyyy-mm-dd hh24:mi:ss'), 16);

DUMP(TO_DATE('2023-03-0112:34:56
--------------------------------
Typ=13 Len=8: e7,7,3,1,c,22,38,0

>>> ora_hash(b'\xe7\x07\x03\x01\x0c\x22\x38\x00')
112410422

Specifying max_bucket and seed_value

These parameters are also supported.

MAX_BUCKET

SQL> select ora_hash('abracadabra', 255);

ORA_HASH('ABRACADABRA',255)
---------------------------
                         82

>>> ora_hash(b'abracadabra', 255)
82

SEED_VALUE

SQL> select ora_hash('abracadabra', power(2,32)-1, 123);

ORA_HASH('ABRACADABRA',POWER(2,32)-1,123)
-----------------------------------------
                               4012392341

>>> ora_hash(b'abracadabra', seed=123)
4012392341

MAX_BUCKET and SEED_VALUE

SQL> select ora_hash('abracadabra', 255, 123);

ORA_HASH('ABRACADABRA',255,123)
-------------------------------
                            149

>>> ora_hash(b'abracadabra', 255, 123)
149

X$ tables: reading X$KQFCO from Oracle binary

Column definition structures are stored in kqftap:

[oracle@db-21 ~]$ xinfo list 'X$KCCCP' -o json --with-kqftap
{
  "457": {
    "obj": 4294951392,
    "ver": 5,
    "nam_ptr": 383999376,
    "nam": "X$KCCCP",
    "xstruct_nam_ptr": 383999384,
    "xstruct": "kctcpx",
    "typ": 5,
    "flg": 0,
    "rsz": 552,
    "coc": 25,
    "kqftap": {
      "xstruct_ptr": 382889376,
      "cb2_ptr": 75535856,
      "xstruct": "kctcpx_c",
      "cb2": "kctxcp"
    }
  }
}

For example, the structure kctcpx_c defines X$KCCCP's columns:

[oracle@db-21 bin]$ readelf -s oracle | grep -E -A1 -w 'Symbol|kctcpx_c' --no-group-separator
Symbol table '.dynsym' contains 225083 entries:
   Num:    Value          Size Type    Bind   Vis      Ndx Name
Symbol table '.symtab' contains 402534 entries:
   Num:    Value          Size Type    Bind   Vis      Ndx Name
 80301: 0000000016d77100  1664 OBJECT  LOCAL  DEFAULT   17 kctcpx_c
 80302: 0000000016d77780  2304 OBJECT  LOCAL  DEFAULT   17 kctmtx_c

Here is what it looks like:

[oracle@db-21 bin]$ objdump -s --start-address=0x0000000016d77100 --stop-address=$((0x0000000016d77100+1664)) oracle

oracle:     file format elf64-x86-64

Contents of section .rodata:
 16d77100 04000000 00000000 9c95e616 00000000  ................
 16d77110 17090000 00000000 00000000 00000000  ................
 16d77120 00000000 00000000 08000000 00000000  ................
 16d77130 00000000 00000000 00000000 00000000  ................
 16d77140 04000000 00000000 60d6d715 00000000  ........`.......
 16d77150 020b0000 00000000 00000000 00000000  ................
 16d77160 00000000 00000000 04000000 00000000  ................
 16d77170 00000000 00000000 00000000 00000000  ................
 16d77180 07000000 00000000 b0960f16 00000000  ................
 16d77190 020b0000 00000000 00000000 00000000  ................
 16d771a0 00000000 00000000 04000000 00000000  ................
 16d771b0 00000000 00000000 00000000 00000000  ................
 16d771c0 06000000 00000000 5c8de716 00000000  ........\.......
 16d771d0 020b0000 00000000 00000000 00000000  ................
 16d771e0 00000000 00000000 02000000 00000000  ................
 16d771f0 00000000 00000000 00000000 00000000  ................
 16d77200 05000000 00000000 6c82e916 00000000  ........l.......
 16d77210 02000100 00000000 00000000 00000000  ................
 16d77220 00000000 00000000 04000000 00000000  ................
 16d77230 0c020000 00000000 00000000 00000000  ................

Each column is defined within 64 bytes:

[oracle@db-21 bin]$ for a in 16e6959c 15d7d660 160f96b0 16e78d5c 16e9826c
> do
>   objdump -s --start-address=0x$a --stop-address=$((0x$a+16)) oracle
> done

oracle:     file format elf64-x86-64

Contents of section .rodata:
 16e6959c 41444452 00000000 6b71665f 66696c6c  ADDR....kqf_fill

oracle:     file format elf64-x86-64

Contents of section .rodata:
 15d7d660 494e4458 00000000 49535355 45440000  INDX....ISSUED..

oracle:     file format elf64-x86-64

Contents of section .rodata:
 160f96b0 494e5354 5f494400 71656573 46696e64  INST_ID.qeesFind

oracle:     file format elf64-x86-64

Contents of section .rodata:
 16e78d5c 434f4e5f 49440000 5352435f 434f4e5f  CON_ID..SRC_CON_

oracle:     file format elf64-x86-64

Contents of section .rodata:
 16e9826c 4350544e 4f000000 43505354 41000000  CPTNO...CPSTA...

Here is the structure of X$KCCCP:

SQL> desc x$kcccp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CON_ID                                             NUMBER
 CPTNO                                              NUMBER
 CPSTA                                              NUMBER
 CPFLG                                              NUMBER
 CPDRT                                              NUMBER
 CPRDB                                              NUMBER
 CPLRBA_SEQ                                         NUMBER
 CPLRBA_BNO                                         NUMBER
 CPLRBA_BOF                                         NUMBER
 CPODR_SEQ                                          NUMBER
 CPODR_BNO                                          NUMBER
 CPODR_BOF                                          NUMBER
 CPODS                                              VARCHAR2(20)
 CPODT                                              VARCHAR2(20)
 CPODT_I                                            NUMBER
 CPHBT                                              NUMBER
 CPRLS                                              VARCHAR2(20)
 CPRLC                                              NUMBER
 CPMID                                              NUMBER
 CPSDR_SEQ                                          NUMBER
 CPSDR_BNO                                          NUMBER
 CPSDR_ADB                                          NUMBER

And the actual X$KQFCO rows:

SQL> select kqfcocno,
  2         kqfconam,
  3         kqfcosiz,
  4         kqfcodty,
  5         kqfcotyp,
  6         kqfcomax,
  7         kqfcolsz,
  8         kqfcolof,
  9         kqfcooff,
 10         kqfcoidx,
 11         kqfcoipo
 12    from x$kqfta t,
 13         x$kqfco c
 14   where t.kqftanam = 'X$KCCCP'
 15     and c.kqfcotab = t.indx
 16   order by 1;

  KQFCOCNO KQFCONAM     KQFCOSIZ   KQFCODTY   KQFCOTYP   KQFCOMAX   KQFCOLSZ   KQFCOLOF   KQFCOOFF   KQFCOIDX   KQFCOIPO
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 ADDR                8         23          9          0          0          0          0          0          0
         2 INDX                4          2         11          0          0          0          0          0          0
         3 INST_ID             4          2         11          0          0          0          0          0          0
         4 CON_ID              2          2         11          0          0          0          0          0          0
         5 CPTNO               4          2          0          0          0          0        524          1          0
         6 CPSTA               4          2         11          0          0          0          0          0          0
         7 CPFLG               4          2         11          0          0          0          4          0          0
         8 CPDRT               4          2         11          0          0          0          8          0          0
         9 CPRDB               4          2         11          0          0          0        548          0          0
        10 CPLRBA_SEQ          4          2         11          0          0          0         12          0          0
        11 CPLRBA_BNO          4          2         11          0          0          0         16          0          0
        12 CPLRBA_BOF          2          2         11          0          0          0         20          0          0
        13 CPODR_SEQ           4          2         11          0          0          0         24          0          0
        14 CPODR_BNO           4          2         11          0          0          0         28          0          0
        15 CPODR_BOF           2          2         11          0          0          0         32          0          0
        16 CPODS              20          1          5          0          0          0        484          0          0
        17 CPODT              20          1          5          0          0          0        504          0          0
        18 CPODT_I             4          2         11          0          0          0         44          0          0
        19 CPHBT               4          2         11          0          0          0         60          0          0
        20 CPRLS              20          1          5          0          0          0        528          0          0
        21 CPRLC               4          2         11          0          0          0         48          0          0
        22 CPMID               4          2         11          0          0          0         64          0          0
        23 CPSDR_SEQ           4          2         11          0          0          0        104          0          0
        24 CPSDR_BNO           4          2         11          0          0          0        108          0          0
        25 CPSDR_ADB           2          2         11          0          0          0        112          0          0

25 rows selected.

The xinfo tool has a describe command that produces the same data:

[oracle@db-21 ~]$ xinfo desc 'X$KCCCP'
+-----+------------+------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+
| cno |    nam_ptr | nam        | siz | dty | typ | max | lsz | lof | off | idx | ipo | kqfcop_indx |
+-----+------------+------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+
|   1 | 0x16e6959c | ADDR       |   8 |  23 |   9 |   0 |   0 |   0 |   0 |   0 |   0 |           0 |
|   2 | 0x15d7d660 | INDX       |   4 |   2 |  11 |   0 |   0 |   0 |   0 |   0 |   0 |           0 |
|   3 | 0x160f96b0 | INST_ID    |   4 |   2 |  11 |   0 |   0 |   0 |   0 |   0 |   0 |           0 |
|   4 | 0x16e78d5c | CON_ID     |   2 |   2 |  11 |   0 |   0 |   0 |   0 |   0 |   0 |           0 |
|   5 | 0x16e9826c | CPTNO      |   4 |   2 |   0 |   0 |   0 |   0 | 524 |   1 |   0 |           0 |
|   6 | 0x16e98274 | CPSTA      |   4 |   2 |  11 |   0 |   0 |   0 |   0 |   0 |   0 |           0 |
|   7 | 0x16e9827c | CPFLG      |   4 |   2 |  11 |   0 |   0 |   0 |   4 |   0 |   0 |           0 |
|   8 | 0x16e98284 | CPDRT      |   4 |   2 |  11 |   0 |   0 |   0 |   8 |   0 |   0 |           0 |
|   9 | 0x16e9828c | CPRDB      |   4 |   2 |  11 |   0 |   0 |   0 | 548 |   0 |   0 |           0 |
|  10 | 0x16e98294 | CPLRBA_SEQ |   4 |   2 |  11 |   0 |   0 |   0 |  12 |   0 |   0 |           0 |
|  11 | 0x16e982a0 | CPLRBA_BNO |   4 |   2 |  11 |   0 |   0 |   0 |  16 |   0 |   0 |           0 |
|  12 | 0x16e982ac | CPLRBA_BOF |   2 |   2 |  11 |   0 |   0 |   0 |  20 |   0 |   0 |           0 |
|  13 | 0x16e982b8 | CPODR_SEQ  |   4 |   2 |  11 |   0 |   0 |   0 |  24 |   0 |   0 |           0 |
|  14 | 0x16e982c4 | CPODR_BNO  |   4 |   2 |  11 |   0 |   0 |   0 |  28 |   0 |   0 |           0 |
|  15 | 0x16e982d0 | CPODR_BOF  |   2 |   2 |  11 |   0 |   0 |   0 |  32 |   0 |   0 |           0 |
|  16 | 0x16e982dc | CPODS      |  20 |   1 |   5 |   0 |   0 |   0 | 484 |   0 |   0 |           0 |
|  17 | 0x16e982e4 | CPODT      |  20 |   1 |   5 |   0 |   0 |   0 | 504 |   0 |   0 |           0 |
|  18 | 0x16e982ec | CPODT_I    |   4 |   2 |  11 |   0 |   0 |   0 |  44 |   0 |   0 |           0 |
|  19 | 0x16e982f4 | CPHBT      |   4 |   2 |  11 |   0 |   0 |   0 |  60 |   0 |   0 |           0 |
|  20 | 0x16e982fc | CPRLS      |  20 |   1 |   5 |   0 |   0 |   0 | 528 |   0 |   0 |           0 |
|  21 | 0x16e98304 | CPRLC      |   4 |   2 |  11 |   0 |   0 |   0 |  48 |   0 |   0 |           0 |
|  22 | 0x16e9830c | CPMID      |   4 |   2 |  11 |   0 |   0 |   0 |  64 |   0 |   0 |           0 |
|  23 | 0x16e98314 | CPSDR_SEQ  |   4 |   2 |  11 |   0 |   0 |   0 | 104 |   0 |   0 |           0 |
|  24 | 0x16e98320 | CPSDR_BNO  |   4 |   2 |  11 |   0 |   0 |   0 | 108 |   0 |   0 |           0 |
|  25 | 0x16e9832c | CPSDR_ADB  |   2 |   2 |  11 |   0 |   0 |   0 | 112 |   0 |   0 |           0 |
+-----+------------+------------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-------------+

X$ tables: table-level callbacks and column definitions (without parsing)

Thus far, I have reviewed the kqftab structure which is used to build the majority of tables in X$KQFTA. It is not clear yet where actual columns are coming from. There is an additional structure called kqftap that has the extra information.

[oracle@db-21 bin]$ readelf -s oracle | grep -E -A1 -w 'Symbol|kqftap' --no-group-separator
Symbol table '.dynsym' contains 225083 entries:
   Num:    Value          Size Type    Bind   Vis      Ndx Name
225049: 0000000016cf4d00 40736 OBJECT  GLOBAL DEFAULT   17 kqftap
225050: 0000000007b02100   256 FUNC    GLOBAL DEFAULT   13 l9_ippsRLEGetInUseTable_8
Symbol table '.symtab' contains 402534 entries:
   Num:    Value          Size Type    Bind   Vis      Ndx Name
351482: 0000000016cf4d00 40736 OBJECT  GLOBAL DEFAULT   17 kqftap
351483: 0000000012aef270   800 FUNC    GLOBAL DEFAULT   14 qesxlGetPayloadData

Here is what it looks like:

[oracle@db-21 bin]$ objdump -s --start-address=0x0000000016cf4d00 --stop-address=$((0x0000000016cf4d00+40736)) oracle

oracle:     file format elf64-x86-64

Contents of section .rodata:
 16cf4d00 00000000 00000000 00efcf16 00000000  ................
 16cf4d10 7039670e 00000000 00000000 00000000  p9g.............
 16cf4d20 00000000 00000000 00f2cf16 00000000  ................
 16cf4d30 00000000 00000000 00000000 00000000  ................
 16cf4d40 00000000 00000000 00f4cf16 00000000  ................
 16cf4d50 00000000 00000000 00000000 00000000  ................
 16cf4d60 00000000 00000000 80f5cf16 00000000  ................
 16cf4d70 00000000 00000000 00000000 00000000  ................
 16cf4d80 00000000 00000000 80f7cf16 00000000  ................
 16cf4d90 903b670e 00000000 00000000 00000000  .;g.............
..

It is made of 32-byte rows. The third column in the output points to the X$-column structure - the structure describing the corresponding X$ table:

[oracle@db-21 bin]$ for a in 16cfef00 16cff200 16cff400 16cff580
> do
>   nm oracle | grep $a
> done
0000000016cfef00 r kqfta_c
0000000016cff200 r kqfvi_c
0000000016cff400 r kqfvt_c
0000000016cff580 r kqfdt_c

[oracle@db-21 ~]$ xinfo list | head -10
+------------+-----+------------+-------------------------------+-----------------+---------------------------+-----+------+--------+-----+
|        obj | ver |    nam_ptr | nam                           | xstruct_nam_ptr | xstruct                   | typ |  flg |    rsz | coc |
+------------+-----+------------+-------------------------------+-----------------+---------------------------+-----+------+--------+-----+
| 4294950912 |   6 | 0x16282d00 | X$KQFTA                       |      0x16e33810 | kqftv                     |   4 |    0 |     80 |  11 |
| 4294950913 |   3 | 0x16e33818 | X$KQFVI                       |      0x16e33810 | kqftv                     |   1 |    0 |     80 |   7 |
| 4294951149 |   3 | 0x16e33820 | X$KQFVT                       |      0x16e33828 | kqftp                     |   1 |    0 |     32 |   5 |
| 4294950914 |   4 | 0x16e33830 | X$KQFDT                       |      0x16e33838 | kqfdt                     |   1 |    0 |     40 |   7 |
| 4294951036 |   6 | 0x16e33840 | X$KQFCO                       |      0x16e33848 | kqfcc                     |   4 |    2 |     80 |  17 |
| 4294952712 |   2 | 0x16e33850 | X$KQFOPT                      |      0x16e3385c | kqfopt                    |   1 |    0 |     24 |   6 |
| 4294952922 |   3 | 0x16e33864 | X$KYWMPCTAB                   |      0x16e33870 | kywmpctab                 |   4 |    0 |     88 |  12 |

Both kqftab and kqftap define tables and corresponding column structures in the same order: X$KQFTA's columns are described in kqfta_c, X$KQFVI's columns are described in kqfvi_c, etc.

Then, some X$ tables require additional processing, so that there are callback functions:

[oracle@db-21 bin]$ nm oracle | grep -E 'e673970|e673b90'
000000000e673970 T kqftbl_cb
000000000e673b90 T kqftco

The xinfo tool was enhanced to output the corresponding kqftap rows when the --with-kqftap option is specified:

[oracle@db-21 ~]$ xinfo list 'X$KSMLRU' --with-kqftap -o json
{
  "88": {
    "obj": 4294951099,
    "ver": 7,
    "nam_ptr": 383991380,
    "nam": "X$KSMLRU",
    "xstruct_nam_ptr": 383991392,
    "xstruct": "ksmlr",
    "typ": 4,
    "flg": 0,
    "rsz": 112,
    "coc": 18,
    "kqftap": {
      "xstruct_ptr": 382474144,
      "cb1_ptr": 132689584,
      "xstruct": "ksmlru_c",
      "cb1": "ksmlrs"
    }
  }
}

I will explain how to extract the column definitions in the next post.

Virtual X$ tables

Some of X$ tables are not coming from the kqftab structure. For example, the xinfo tool outputs only 1,272 tables in 21.5, whereas X$KQFTA has 1,377 rows:

[oracle@db-21 sf_exchange]$ xinfo list -o json |
>   jq -r '.[] | .nam' |
>   awk '{printf "%4d %s\n",NR,$0}'
   1 X$KQFTA
   2 X$KQFVI
   3 X$KQFVT
   4 X$KQFDT
   5 X$KQFCO
..
1271 X$BCAPPLY_STATS
1272 X$FSDDBFS

What about extra 105 rows? Let me show what the output of X$KQFTA is like in my database:

SQL> select * from x$kqfta;

ADDR                   INDX    INST_ID     CON_ID   KQFTAOBJ   KQFTAVER KQFTANAM                         KQFTATYP   KQFTAFLG   KQFTARSZ   KQFTACOC
---------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
0000000016CDBF20          0          1          0 4294950912          6 X$KQFTA                                 4          0         80         11
0000000016CDBF70          1          1          0 4294950913          3 X$KQFVI                                 1          0         80          7
..
0000000016CF4C00       1270          1          0 4294956360          2 X$BCAPPLY_STATS                         4          2        260         16
0000000016CF4C50       1271          1          0 4294956225          2 X$FSDDBFS                               4          0       1144         14
000000008F8127C0       1272          1          0 4294953644          0 X$KSIPC_PROC_STATS                      9         18          0          0
000000008F812810       1273          1          0 4294953645          0 X$KSIPC_INFO                            9         18          0          0
000000008F812860       1274          1          0 4294952215          1 X$KSXPTESTTBL                          10          2        146         15
000000008F8128B0       1275          1          0 4294952216          0 X$KSXP_STATS                            9         18          0          0
000000008F812900       1276          1          0 4294952217          0 X$SKGXP_PORT                            9         18          0          0
000000008F812950       1277          1          0 4294952218          0 X$SKGXP_CONNECTION                      9         18          0          0
000000008F8129A0       1278          1          0 4294952219          0 X$SKGXP_MISC                            9         18          0          0
000000008F8129F0       1279          1          0 4294952227          1 X$KTCNQROW                              9          2       1480         47
..
000000008F8147F0       1375          1          0 4294954950          3 X$DIAG_VTEST_EXISTS                    10         34       1536         13
000000008F814840       1376          1          0 4294954964          3 X$DIAG_VADR_CONTROL                    10         34       1072         21

1377 rows selected.

It can be seen that starting at INDX=1,272 (which is row 1,273 because INDX starts at 0) the ADDR value is quite different - it is 0x8F8127C0. It is not an address from the Oracle binary anymore:

SQL> select *
  2    from x$ksmsp
  3   where to_number('8F8127C0','XXXXXXXXXXXXXXXX')
  4           between to_number(ksmchptr,'XXXXXXXXXXXXXXXX')
  5               and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1;

ADDR                   INDX    INST_ID     CON_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007F6C5E50DC10     106576          1          1          1          1 KQF runtime def  000000008F8127B0       8416 perm              0 000000008F101000

The address refers to the KQF runtime def area of SGA. The X$ tables starting from INDX=1,272 are what Oracle is called virtual tables in its code. Of course, the INDX value is version dependent but these virtual tables always come after other X$ tables defined in kqftab. The starting address of the virtual tables can be obtained from the kqftvrt_ SGA variable:

SQL> oradebug dumpvar sga kqftvrt_
struct kqftv* kqftvrt_ [0600A7408, 0600A7410) = 8F8127C0 00000000

The virtual tables are built inside the kqfbldtvrt function.

X$ tables: reading X$KQFTA from Oracle binary

X$ tables are visible in X$KQFTA, for example:

SQL> select * from x$kqfta where rownum<=10;

ADDR                   INDX    INST_ID     CON_ID   KQFTAOBJ   KQFTAVER KQFTANAM       KQFTATYP   KQFTAFLG   KQFTARSZ   KQFTACOC
---------------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ----------
0000000016CDBF20          0          1          0 4294950912          6 X$KQFTA               4          0         80         11
0000000016CDBF70          1          1          0 4294950913          3 X$KQFVI               1          0         80          7
0000000016CDBFC0          2          1          0 4294951149          3 X$KQFVT               1          0         32          5
0000000016CDC010          3          1          0 4294950914          4 X$KQFDT               1          0         40          7
0000000016CDC060          4          1          0 4294951036          6 X$KQFCO               4          2         80         17
0000000016CDC0B0          5          1          0 4294952712          2 X$KQFOPT              1          0         24          6
0000000016CDC100          6          1          0 4294952922          3 X$KYWMPCTAB           4          0         88         12
0000000016CDC150          7          1          0 4294953009          2 X$KYWMWRCTAB          4          0         72          6
0000000016CDC1A0          8          1          0 4294952923          2 X$KYWMCLTAB           4          0       4076          7
0000000016CDC1F0          9          1          0 4294952924          5 X$KYWMNF              4          0        240         11

This information is mostly coming from the kqftab structure within the Oracle binary:

[oracle@db-21 bin]$ readelf -s oracle | grep -E -A1 -w 'Symbol|kqftab' --no-group-separator
Symbol table '.dynsym' contains 225083 entries:
   Num:    Value          Size Type    Bind   Vis      Ndx Name
224955: 0000000016cdbf20 0x18dd0 OBJECT  GLOBAL DEFAULT   17 kqftab
224956: 00000000058400a0   288 FUNC    GLOBAL DEFAULT   13 kole_length
Symbol table '.symtab' contains 402534 entries:
   Num:    Value          Size Type    Bind   Vis      Ndx Name
344560: 0000000016cdbf20 0x18dd0 OBJECT  GLOBAL DEFAULT   17 kqftab
344561: 0000000016bc7a98    40 OBJECT  GLOBAL DEFAULT   17 kqreqv

This is the actual kqftab structure:

[oracle@db-21 bin]$ objdump -s --start-address=0x0000000016cdbf20 --stop-address=$((0x0000000016cdbf20+0x18dd0)) oracle

oracle:     file format elf64-x86-64

Contents of section .rodata:
 16cdbf20 07000000 00000000 80f52c16 00000000  ..........,.....
 16cdbf30 05000000 00000000 703be816 00000000  ........p;......
 16cdbf40 04000000 00000000 50000000 00000000  ........P.......
 16cdbf50 00000000 00000000 0b000000 02000000  ................
 16cdbf60 00c0ffff 06000000 00000000 00000000  ................
 16cdbf70 07000000 00000000 783be816 00000000  ........x;......
 16cdbf80 05000000 00000000 703be816 00000000  ........p;......
 16cdbf90 01000000 00000000 50000000 00000000  ........P.......
 16cdbfa0 00000000 00000000 07000000 00000000  ................
 16cdbfb0 01c0ffff 03000000 00000000 00000000  ................
 16cdbfc0 07000000 00000000 803be816 00000000  .........;......
 16cdbfd0 05000000 00000000 883be816 00000000  .........;......
 16cdbfe0 01000000 00000000 20000000 00000000  ........ .......
 16cdbff0 00000000 00000000 05000000 00000000  ................
 16cdc000 edc0ffff 03000000 00000000 00000000  ................
...

There is a visible pattern in this data. The highlighted lines contain some addresses which can be easily dereferenced:

[oracle@db-21 bin]$ for a in 162cf580 16e83b78 16e83b80
> do
>   objdump -s --start-address=0x$a --stop-address=$((0x$a+16)) oracle
> done

oracle:     file format elf64-x86-64

Contents of section .rodata:
 162cf580 58244b51 46544100 716b736f 7050726f  X$KQFTA.qksopPro

oracle:     file format elf64-x86-64

Contents of section .rodata:
 16e83b78 58244b51 46564900 58244b51 46565400  X$KQFVI.X$KQFVT.

oracle:     file format elf64-x86-64

Contents of section .rodata:
 16e83b80 58244b51 46565400 6b716674 70000000  X$KQFVT.kqftp...

Not surprisingly, the order of tables is the same as in X$KQFTA. It made me think that the structure kqftab can be used to construct an output similar to X$KQFTA. That is why I wrote a simple program that reads the Oracle binary and does just that:

[oracle@db-21 ~]$ xinfo list
+------------+-----+------------+-------------------------------+-----------------+---------------------------+-----+------+--------+-----+
|        obj | ver |    nam_ptr | nam                           | xstruct_nam_ptr | xstruct                   | typ |  flg |    rsz | coc |
+------------+-----+------------+-------------------------------+-----------------+---------------------------+-----+------+--------+-----+
| 4294950912 |   6 | 0x16282d00 | X$KQFTA                       |      0x16e33810 | kqftv                     |   4 |    0 |     80 |  11 |
| 4294950913 |   3 | 0x16e33818 | X$KQFVI                       |      0x16e33810 | kqftv                     |   1 |    0 |     80 |   7 |
| 4294951149 |   3 | 0x16e33820 | X$KQFVT                       |      0x16e33828 | kqftp                     |   1 |    0 |     32 |   5 |
| 4294950914 |   4 | 0x16e33830 | X$KQFDT                       |      0x16e33838 | kqfdt                     |   1 |    0 |     40 |   7 |
| 4294951036 |   6 | 0x16e33840 | X$KQFCO                       |      0x16e33848 | kqfcc                     |   4 |    2 |     80 |  17 |
| 4294952712 |   2 | 0x16e33850 | X$KQFOPT                      |      0x16e3385c | kqfopt                    |   1 |    0 |     24 |   6 |
| 4294952922 |   3 | 0x16e33864 | X$KYWMPCTAB                   |      0x16e33870 | kywmpctab                 |   4 |    0 |     88 |  12 |
| 4294953009 |   2 | 0x16e3387c | X$KYWMWRCTAB                  |      0x16e3388c | kywmwrctab                |   4 |    0 |     72 |   6 |
| 4294952923 |   2 | 0x16e33898 | X$KYWMCLTAB                   |      0x16e338a4 | kywmcltab                 |   4 |    0 |   4076 |   7 |
| 4294952924 |   5 | 0x16e338b0 | X$KYWMNF                      |      0x16e338bc | kywmnf                    |   4 |    0 |    240 |  11 |
...
| 4294956357 |   2 | 0x16e3a834 | X$CONSENSUS_STATS             |      0x16e3a848 | kconsstats                |   4 |    2 |     64 |  10 |
| 4294956360 |   2 | 0x16e3a854 | X$BCAPPLY_STATS               |      0x16e3a864 | kbbaStats                 |   4 |    2 |    260 |  16 |
| 4294956225 |   2 | 0x16e3a870 | X$FSDDBFS                     |      0x16e3a87c | fsddbfs                   |   4 |    0 |   1144 |  14 |
+------------+-----+------------+-------------------------------+-----------------+---------------------------+-----+------+--------+-----+

[oracle@db-21 ~]$ xinfo list 'X$KCCC*'
+------------+-----+------------+---------+-----------------+---------+-----+-----+-----+-----+
|        obj | ver |    nam_ptr | nam     | xstruct_nam_ptr | xstruct | typ | flg | rsz | coc |
+------------+-----+------------+---------+-----------------+---------+-----+-----+-----+-----+
| 4294951110 |   5 | 0x16e3553c | X$KCCCF |      0x16e35544 | kcccf   |   4 |   0 | 532 |   9 |
| 4294951215 |   4 | 0x16e3567c | X$KCCCC |      0x16e35684 | kcccc   |   5 |   6 |  48 |  14 |
| 4294951392 |   5 | 0x16e35d90 | X$KCCCP |      0x16e35d98 | kctcpx  |   5 |   0 | 552 |  25 |
+------------+-----+------------+---------+-----------------+---------+-----+-----+-----+-----+

I also make use of other information that is stored in kqftab and add a column called xstruct to the output.

Tracing Library Cache Locks Using _kgl_debug

A recent blog post from Nenad Noveljic prompted me to review the library cache tracing facilities available in Oracle database.

After a bit of tinkering I discovered the actual library cache lock tracing is governed by the _kgl_debug parameter.

Here is a little sample code to demonstrate that - I borrowed part of it from the excellent Nenad's blog post:

SQL> create table t (n1 integer,n2 integer);

Table created.

SQL> create index ix_t on t(n1,n2);

Index created.

SQL> exec dbms_stats.gather_table_stats ('', 'T', cascade => true)

PL/SQL procedure successfully completed.

SQL>
SQL> alter system set "_kgl_debug"=32 scope=memory;

System altered.

SQL>
SQL> alter index ix_t invisible ;

Index altered.

In a nutshell, _kgl_debug=32 appears to result in writing information about library cache locks in the trace file. Here is how it looks:

SQL> col trace_file old_v tf for a72
SQL> col dirname old_v dn for a50
SQL> col basename old_v bn for a21
SQL>
SQL> select value trace_file,
  2         substr(value, 1, instr(value, '/', -1)-1) dirname,
  3         substr(value, instr(value, '/', -1)+1) basename
  4    from v$diag_info
  5   where name='Default Trace File';

TRACE_FILE                                                               DIRNAME                                            BASENAME
------------------------------------------------------------------------ -------------------------------------------------- ---------------------
/u01/app/oracle/diag/rdbms/racdba/racdba1/trace/racdba1_ora_16440.trc    /u01/app/oracle/diag/rdbms/racdba/racdba1/trace    racdba1_ora_16440.trc

SQL>
SQL> ho tail -64 &tf.
    <Mode>N</Mode>
  </LibraryObjectLock>
</KGLTRACE>
<KGLTRACE>
  <Timestamp>2021-02-12 15:41:23.656</Timestamp>
  <SID>136</SID>
  <Function>kglLock</Function>
  <Reason>TRACELOCK</Reason>
  <Param1>0x706c20e0</Param1>
  <Param2>1</Param2>
  <LibraryHandle>
    <Address>0x67f78888</Address>
    <Hash>94e2179b</Hash>
    <LockMode>N</LockMode>
    <PinMode>0</PinMode>
    <LoadLockMode>0</LoadLockMode>
    <Status>VALD</Status>
    <ObjectName>
      <Name>select value trace_file,
       substr(value, 1, instr(value, '/', -1)-1) dirname,
       substr(value, instr(value, '/', -1)+1) basename
  from v$diag_info
 where name='Default Trace File'</Name>
      <FullHashValue>8c302f585b9b9a83239f686f94e2179b</FullHashValue>
      <Namespace>SQL AREA(00)</Namespace>
      <Type>CURSOR(00)</Type>
      <ContainerId>1</ContainerId>
      <ContainerUid>1</ContainerUid>
      <Identifier>2497845147</Identifier>
      <OwnerIdn>89</OwnerIdn>
    </ObjectName>
  </LibraryHandle>
  <LibraryObjectLock>
    <Address>0x706c20e0</Address>
    <Handle>0x67f78888</Handle>
    <Mode>N</Mode>
  </LibraryObjectLock>
</KGLTRACE>
<KGLTRACE>
  <Timestamp>2021-02-12 15:41:23.657</Timestamp>
  <SID>136</SID>
  <Function>kgllkal</Function>
  <Reason>TRACELOCK</Reason>
  <Param1>0x706c1ff8</Param1>
  <Param2>0</Param2>
  <LibraryHandle>
    <Address>0x67f78328</Address>
    <Hash>0</Hash>
    <LockMode>N</LockMode>
    <PinMode>0</PinMode>
    <LoadLockMode>0</LoadLockMode>
    <Status>VALD</Status>
    <Name>
      <Namespace>SQL AREA(00)</Namespace>
      <Type>CURSOR(00)</Type>
      <ContainerId>3</ContainerId>
    </Name>
  </LibraryHandle>
  <LibraryObjectLock>
    <Address>0x706c1ff8</Address>
    <Handle>0x67f78328</Handle>
    <Mode>N</Mode>
  </LibraryObjectLock>
</KGLTRACE>

It is quite convenient that the trace data is provided in XML - it can be parsed easily:

SQL> create or replace directory trace_dir as '&dn.';
old   1: create or replace directory trace_dir as '&dn.'
new   1: create or replace directory trace_dir as '/u01/app/oracle/diag/rdbms/racdba/racdba1/trace'

Directory created.

SQL>
SQL> create table trace_ext (
  2    trace_data clob
  3  )
  4  organization external (
  5    type oracle_loader
  6    default directory trace_dir
  7    access parameters (
  8      records
  9      xmltag ("KGLTRACE")
 10      fields ldrtrim
 11      missing field values are null (
 12        trace_data char(1000000)
 13      )
 14    )
 15    location ('&bn.')
 16  )
 17  reject limit unlimited;
old  15:   location ('&bn.')
new  15:   location ('racdba1_ora_16440.trc')

Table created.

SQL>
SQL> select count(*) from trace_ext;

  COUNT(*)
----------
       275

SQL> ho grep KGLTRACE &tf. | wc -l
550

Looks good. 275 rows in the external table for 550 KGLTRACE tags - these are both opening and closing tags, so that the number of rows matches precisely the number of XML elements in the trace file.

Finally, we can retrieve the information about kgllkal calls for interesting objects:

SQL> select xt."Timestamp",
  2         xt."Function",
  3         xt."Reason",
  4         xt."Param1",
  5         lh."LockMode",
  6         lh."PinMode",
  7         obj."Name",
  8         obj."Namespace",
  9         obj."Type",
 10         lol."Address" lol_address,
 11         lol."Mode" lol_mode
 12    from trace_ext,
 13         xmltable('/KGLTRACE' passing xmltype(trace_data)
 14           columns "Timestamp" varchar2(24),
 15                   sid number,
 16                   "Function" varchar2(20),
 17                   "Reason" varchar2(10),
 18                   "Param1" varchar2(14),
 19                   "Param2" number,
 20                   "LibraryHandle" xmltype,
 21                   "LibraryObjectLock" xmltype
 22         )(+) xt,
 23         xmltable('/LibraryHandle' passing xt."LibraryHandle"
 24           columns "Address" varchar2(10),
 25                   "Hash" varchar2(10),
 26                   "LockMode" varchar2(8),
 27                   "PinMode" varchar2(8),
 28                   "LoadLockMode" varchar2(8),
 29                   "Status" varchar2(10),
 30                   "ObjectName" xmltype
 31         )(+) lh,
 32         xmltable('/ObjectName' passing lh."ObjectName"
 33           columns "Name" varchar2(64),
 34                   "FullHashValue" varchar2(32),
 35                   "Namespace" varchar2(32),
 36                   "Type" varchar2(32),
 37                   "ContainerId" number,
 38                   "ContainerUid" number,
 39                   "Identifier" number,
 40                   "OwnerIdn" number
 41         )(+) obj,
 42         xmltable('/LibraryObjectLock' passing xt."LibraryObjectLock"
 43           columns "Address" varchar2(10),
 44                   "Handle" varchar2(10),
 45                   "Mode"   varchar2(4)
 46         )(+) lol
 47   where 1=1
 48     and obj."Name" like '%PDB.TC.%'
 49     and xt."Function"='kgllkal';

Timestamp                Function Reason     Param1         LockMode PinMode  Name            Namespace            Type       LOL_ADDRES LOL_MODE
------------------------ -------- ---------- -------------- -------- -------- --------------- -------------------- ---------- ---------- --------
2021-02-12 15:41:23.595  kgllkal  TRACELOCK  0x62da2ef0     S        0        PDB.TC.IX_T     INDEX(04)            INDEX(01)  0x62da2ef0 S
2021-02-12 15:41:23.598  kgllkal  TRACELOCK  0x62da2ef0     S        0        PDB.TC.T        TABLE/PROCEDURE(01)  TABLE(02)  0x62da2ef0 S
2021-02-12 15:41:23.599  kgllkal  TRACELOCK  0x62c9f8d0     S        0        PDB.TC.IX_T     INDEX(04)            INDEX(01)  0x62c9f8d0 S
2021-02-12 15:41:23.601  kgllkal  TRACELOCK  0x62c9f8d0     S        S        PDB.TC.T        TABLE/PROCEDURE(01)  TABLE(02)  0x62c9f8d0 S
2021-02-12 15:41:23.613  kgllkal  TRACELOCK  0x65db4480     S        0        PDB.TC.IX_T     INDEX(04)            INDEX(01)  0x65db4480 S
2021-02-12 15:41:23.617  kgllkal  TRACELOCK  0x65db4480     S        0        PDB.TC.T        TABLE/PROCEDURE(01)  TABLE(02)  0x65db4480 S
2021-02-12 15:41:23.618  kgllkal  TRACELOCK  0x62db5708     S        0        PDB.TC.IX_T     INDEX(04)            INDEX(01)  0x62db5708 S
2021-02-12 15:41:23.626  kgllkal  TRACELOCK  0x65db4480     S        0        PDB.TC.IX_T     INDEX(04)            INDEX(01)  0x65db4480 S
2021-02-12 15:41:23.629  kgllkal  TRACELOCK  0x65db4480     X        0        PDB.TC.T        TABLE/PROCEDURE(01)  TABLE(02)  0x65db4480 X
2021-02-12 15:41:23.632  kgllkal  TRACELOCK  0x62da2ef0     X        0        PDB.TC.IX_T     INDEX(04)            INDEX(01)  0x62da2ef0 X

10 rows selected.

Conclusion

We can trace library cache locks, or more specifically certain kgllkal calls. The resulting trace data is written to the trace file in the XML format. It can be loaded into the database for further analysis.

Usual disclaimer

This blog post is a pure speculation. Although the results might be be reasonable and suggestive, I have no idea whether or not _kgl_debug=32 covers all or most library cache locks.

Unwrapping PL/SQL: Using Custom Wrap Tables

The previous article in this series:

1. Unwrapping PL/SQL: a Systematic Approach

As a bonus, I decided to apply that discovered knowledge about Oracle Wrap tables.
Thus, I created my own version of the oracle binary and put trivial wrap tables in it - the ones keeping all bytes the same:

$ diff <(xxd oracle) <(xxd /tmp/oracle_modified)
19775119,19775150c19775119,19775150
-- pkwrap_reverse_table
< 12dbe8e0:dd05 2553 b87b 8227 91f2 4b03 eb55 40ff  ..%S.{.'..K..U@.
< 12dbe8f0:1d08 1b3b c462 c807 2a7e 44c6 bea3 8bb7  ...;.b..*~D.....
< 12dbe900:0fd4 de1a df72 490a af89 d5f6 bf51 edb0  .....rI......Q..
< 12dbe910:1879 1596 5ce1 a421 01a6 994d 7675 c91e  .y..\..!...Mvu..
< 12dbe920:263e 1985 a55a 246c 0ec7 2e50 fd48 933f  &>...Z$l...P.H.?
< 12dbe930:7042 1158 f87d ccd8 39ec e8a7 f584 f32c  pB.X.}..9......,
< 12dbe940:e656 cd45 4fd2 c2e0 7cf0 6341 c52b 3cb6  .V.EO...|.cA.+<.
< 12dbe950:00fc 6f9d ac38 bc74 d70d dcda d088 0cd1  ..o..8.t........
< 12dbe960:e795 d37a e368 83fe b934 8c86 4335 b480  ...z.h...4..C5..
< 12dbe970:3d04 9af9 b565 cf6a 5bab 7f92 375f aa16  =....e.j[...7_..
< 12dbe980:54e9 e4fa bd90 a036 c120 1fba 22d9 ef61  T......6. .."..a
< 12dbe990:4777 ad71 789f b333 108e fb8f 5ea9 5917  Gw.qx..3....^.Y.
< 12dbe9a0:1287 52f4 57ca 6713 3006 c0ae f18d 981c  ..R.W.g.0.......
< 12dbe9b0:2fce 94b2 66cb 236d 4c6b db64 ee60 09d6  /...f.#mLk.d.`..
< 12dbe9c0:02a2 4e28 9c4a e2a8 46e5 f773 3a5d 81c3  ..N(.J..F..s:]..
< 12dbe9d0:2d32 eab1 2914 0b31 9b9e 69a1 8abb 976e  -2..)..1..i....n
---
-- modified pkwrap_reverse_table
> 12dbe8e0:a0a1 a2a3 a4a5 a6a7 a8a9 aaab acad aeaf  ................
> 12dbe8f0:b0b1 b2b3 b4b5 b6b7 b8b9 babb bcbd bebf  ................
> 12dbe900:c0c1 c2c3 c4c5 c6c7 c8c9 cacb cccd cecf  ................
> 12dbe910:d0d1 d2d3 d4d5 d6d7 d8d9 dadb dcdd dedf  ................
> 12dbe920:e0e1 e2e3 e4e5 e6e7 e8e9 eaeb eced eeef  ................
> 12dbe930:f0f1 f2f3 f4f5 f6f7 f8f9 fafb fcfd feff  ................
> 12dbe940:0001 0203 0405 0607 0809 0a0b 0c0d 0e0f  ................
> 12dbe950:1011 1213 1415 1617 1819 1a1b 1c1d 1e1f  ................
> 12dbe960:2021 2223 2425 2627 2829 2a2b 2c2d 2e2f   !"#$%&'()*+,-./
> 12dbe970:3031 3233 3435 3637 3839 3a3b 3c3d 3e3f  0123456789:;<=>?
> 12dbe980:4041 4243 4445 4647 4849 4a4b 4c4d 4e4f  @ABCDEFGHIJKLMNO
> 12dbe990:5051 5253 5455 5657 5859 5a5b 5c5d 5e5f  PQRSTUVWXYZ[\]^_
> 12dbe9a0:6061 6263 6465 6667 6869 6a6b 6c6d 6e6f  `abcdefghijklmno
> 12dbe9b0:7071 7273 7475 7677 7879 7a7b 7c7d 7e7f  pqrstuvwxyz{|}~.
> 12dbe9c0:8081 8283 8485 8687 8889 8a8b 8c8d 8e8f  ................
> 12dbe9d0:9091 9293 9495 9697 9899 9a9b 9c9d 9e9f  ................

-- pkwrap_forward_table
< 12dbe9e0:7038 e00b 9101 c917 11de 27f6 7e79 4820  p8........'.~yH
< 12dbe9f0:b852 c0c7 f532 9fbf 3042 2312 cf10 3faa  .R...2..0B#...?.
< 12dbea00:a937 acd6 4602 4007 e3f4 186d 5ff0 4ad0  .7..F.@....m_.J.
< 12dbea10:c8f7 f1b7 898d a79c 7558 ec13 6e90 414f  ........uX..n.AO
< 12dbea20:0e6b 518c 1a63 e8b0 4d26 e50a d83b e264  .kQ..c..M&...;.d
< 12dbea30:4b2d c203 a00d 61c4 53be 4598 34ed bc9d  K-....a.S.E.4...
< 12dbea40:ddaf 156a db95 d4c6 85fa 97d9 47d7 ff72  ...j........G..r
< 12dbea50:50b3 25eb 773d 3cb1 b431 8305 6855 199a  P.%.w=<..1..hU..
< 12dbea60:8fee 0686 5d43 8bc1 7d29 fc1e 8acd b9bb  ....]C..})......
< 12dbea70:a508 9b4e d281 33fe ce3a 92f8 e473 f9b5  ...N..3..:...s..
< 12dbea80:a6fb e11d 3644 395b e7bd 9e99 74b2 cb28  ....6D9[....t..(
< 12dbea90:2ff3 d3b6 8e94 6f1f 0488 abfd 76a4 1c2c  /.....o.....v..,
< 12dbeaa0:caa8 66ef 146c 1b49 163e c5d5 5662 d196  ..f..l.I.>..Vb..
< 12dbeab0:7c7f 6582 212a df78 57ad 7bda 7a00 2224  |.e.!*.xW.{.z."$
< 12dbeac0:6735 e684 a2e9 6080 5aa1 f20c 592e dcae  g5....`.Z...Y...
< 12dbead0:69cc 095e c35c 2bea 5493 a3ba 714c 870f  i..^.\+.T...qL..
---
-- modified pkwrap_forward_tble
> 12dbe9e0:6061 6263 6465 6667 6869 6a6b 6c6d 6e6f  `abcdefghijklmno
> 12dbe9f0:7071 7273 7475 7677 7879 7a7b 7c7d 7e7f  pqrstuvwxyz{|}~.
> 12dbea00:8081 8283 8485 8687 8889 8a8b 8c8d 8e8f  ................
> 12dbea10:9091 9293 9495 9697 9899 9a9b 9c9d 9e9f  ................
> 12dbea20:a0a1 a2a3 a4a5 a6a7 a8a9 aaab acad aeaf  ................
> 12dbea30:b0b1 b2b3 b4b5 b6b7 b8b9 babb bcbd bebf  ................
> 12dbea40:c0c1 c2c3 c4c5 c6c7 c8c9 cacb cccd cecf  ................
> 12dbea50:d0d1 d2d3 d4d5 d6d7 d8d9 dadb dcdd dedf  ................
> 12dbea60:e0e1 e2e3 e4e5 e6e7 e8e9 eaeb eced eeef  ................
> 12dbea70:f0f1 f2f3 f4f5 f6f7 f8f9 fafb fcfd feff  ................
> 12dbea80:0001 0203 0405 0607 0809 0a0b 0c0d 0e0f  ................
> 12dbea90:1011 1213 1415 1617 1819 1a1b 1c1d 1e1f  ................
> 12dbeaa0:2021 2223 2425 2627 2829 2a2b 2c2d 2e2f   !"#$%&'()*+,-./
> 12dbeab0:3031 3233 3435 3637 3839 3a3b 3c3d 3e3f  0123456789:;<=>?
> 12dbeac0:4041 4243 4445 4647 4849 4a4b 4c4d 4e4f  @ABCDEFGHIJKLMNO
> 12dbead0:5051 5253 5455 5657 5859 5a5b 5c5d 5e5f  PQRSTUVWXYZ[\]^_

Remember, this wrap process, which uses modified substitution tables, should hold the following invariant to keep bytes the same:
MOD(pkwrap_reverse_table[BYTE] + 0x60, 0x100) = pkwrap_forward_table[MOD(BYTE + 0xA0, 0x100)] = BYTE for all BYTEs from 0 to 255 (it was demonstrated in the first part of this series).
Here is a sample package that I wrapped using that modified oracle binary:

SQL> with src as (
  2    select q'!package test_pkg
  3  is
  4  procedure p1;
  5  function f1 return varchar2;
  6  end;!' txt
  7      from dual),
  8    wrap as (
  9      select dbms_ddl.wrap( 'create ' || src.txt ) wrap
 10        from src)
 11  select wrap wrapped_code
 12    from wrap
 13  /

WRAPPED_CODE
--------------------------------------------------------------------------------
create package test_pkg wrapped
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
9
44 81
kuXPwLaDK0xzws78Nfs5dvIfvtl42gtwdPZ2dHdVKEktLokvyE7n8gzmCgjyd3Z1CQ1yVQgw
tOZyC/VzDvH091NwM1QIcg0JDfJTCHMMcvZwDDKy5nL1c7FmAACeSxJa


SQL> with src as (
  2    select q'!package body test_pkg
  3  is
  4  procedure p1
  5  is
  6     v_local_var pls_integer := 10;
  7  begin
  8    dbms_output.put_line(123);
  9  end;
 10  function f1 return varchar2
 11  is
 12    /* code comment*/
 13  begin
 14    return 'string';
 15  end;
 16  end;!' txt
 17      from dual),
 18    wrap as (
 19      select dbms_ddl.wrap( 'create ' || src.txt ) wrap
 20        from src)
 21  select wrap wrapped_code
 22    from wrap
 23  /

WRAPPED_CODE
--------------------------------------------------------------------------------
create package body test_pkg wrapped
a000000
369
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
b8 eb
Fhyioql6UlwVZh69G4MDdFUfl9V42j1NPQvCMBR0zq94W3URUzeLQ5q81mB8Ca9JwSmTFBFE
bP8/tigOd3DcV1D6rFqE2psrTLdxyq/HIGwnAnuNJjFCkIsGgD47r5XLvWIIrsuWIrbIcDiC
3FWixtbSnDP1pcs+xZDidkZ2lnAty/2mEkimEk0iHa0naCQwxsQE86I+KS6/R/+ln1uM0/v+
HIpff6HVB57RL3k=

As it is intended, there is no need to use the pkwrap_reverse_table to unwrap that code - it is enough to unzip it:

SQL> select mycompress.inflate(substr(utl_encode.base64_decode(utl_raw.cast_to_raw('Fhyioql6UlwVZh69G4MDdFUfl9V42j1NPQvCMBR0zq94W3URUzeLQ5q81mB8Ca9JwSmTFBFEbP8/tigOd3DcV1D6rFqE2psrTLdxyq/HIGwnAnuNJjFCkIsGgD47r5XL
vWIIrsuWIrbIcDiC3FWixtbSnDP1pcs+xZDidkZ2lnAty/2mEkimEk0iHa0naCQwxsQE86I+KS6/R/+ln1uM0/v+HIpff6HVB57RL3k=')), 41)) unwrapped_code from dual
  2  /

UNWRAPPED_CODE
----------------------------------------------------
PACKAGE BODY test_pkg
IS
PROCEDURE P1
IS
   V_LOCAL_VAR PLS_INTEGER := 10;
BEGIN
  DBMS_OUTPUT.PUT_LINE(123);
END;
FUNCTION F1 RETURN VARCHAR2
IS

BEGIN
  RETURN 'string';
END;
END;

That wrapped code can be correctly compiled as well:

SQL> drop package test_pkg
  2  /

Package dropped.

SQL> create package test_pkg wrapped
  2  a000000
  3  369
  4  abcd
  5  abcd
  6  abcd
  7  abcd
  8  abcd
  9  abcd
 10  abcd
 11  abcd
 12  abcd
 13  abcd
 14  abcd
 15  abcd
 16  abcd
 17  abcd
 18  abcd
 19  9
 20  44 81
 21  kuXPwLaDK0xzws78Nfs5dvIfvtl42gtwdPZ2dHdVKEktLokvyE7n8gzmCgjyd3Z1CQ1yVQgw
 22  tOZyC/VzDvH091NwM1QIcg0JDfJTCHMMcvZwDDKy5nL1c7FmAACeSxJa
 23  /

Package created.

SQL> create package body test_pkg wrapped
  2  a000000
  3  369
  4  abcd
  5  abcd
  6  abcd
  7  abcd
  8  abcd
  9  abcd
 10  abcd
 11  abcd
 12  abcd
 13  abcd
 14  abcd
 15  abcd
 16  abcd
 17  abcd
 18  abcd
 19  b
 20  b8 eb
 21  Fhyioql6UlwVZh69G4MDdFUfl9V42j1NPQvCMBR0zq94W3URUzeLQ5q81mB8Ca9JwSmTFBFE
 22  bP8/tigOd3DcV1D6rFqE2psrTLdxyq/HIGwnAnuNJjFCkIsGgD47r5XLvWIIrsuWIrbIcDiC
 23  3FWixtbSnDP1pcs+xZDidkZ2lnAty/2mEkimEk0iHa0naCQwxsQE86I+KS6/R/+ln1uM0/v+
 24  HIpff6HVB57RL3k=
 25  /

Package body created.

SQL> exec test_pkg.p1
123

PL/SQL procedure successfully completed.

Thus, the unwrapping can be now done without using that substitution table - I made the step 3 (obfuscation of zipped code) an effective noop operation keeping all bytes in place:

SQL> with wrap as (
  2      select 'create package body test_pkg wrapped
  3  a000000
  4  369
  5  abcd
  6  abcd
  7  abcd
  8  abcd
  9  abcd
 10  abcd
 11  abcd
 12  abcd
 13  abcd
 14  abcd
 15  abcd
 16  abcd
 17  abcd
 18  abcd
 19  abcd
 20  b
 21  b8 eb
 22  Fhyioql6UlwVZh69G4MDdFUfl9V42j1NPQvCMBR0zq94W3URUzeLQ5q81mB8Ca9JwSmTFBFE
 23  bP8/tigOd3DcV1D6rFqE2psrTLdxyq/HIGwnAnuNJjFCkIsGgD47r5XLvWIIrsuWIrbIcDiC
 24  3FWixtbSnDP1pcs+xZDidkZ2lnAty/2mEkimEk0iHa0naCQwxsQE86I+KS6/R/+ln1uM0/v+
 25  HIpff6HVB57RL3k=' wrap
 26        from dual),
 27    base64_dcd as(
 28      select substr( utl_encode.base64_decode( utl_raw.cast_to_raw(rtrim( substr( wrap.wrap, instr( wrap.wrap, chr( 10 ), 1, 20 ) + 1 ), chr(10) )  ) ), 41 ) x
 29        from wrap),
 30    subst as (
 31      select x s
 32      from base64_dcd)
 33  select mycompress.inflate( s ) unwrapped_code
 34    from subst
 35  /

UNWRAPPED_CODE
----------------------------------------
PACKAGE BODY test_pkg
IS
PROCEDURE P1
IS
   V_LOCAL_VAR PLS_INTEGER := 10;
BEGIN
  DBMS_OUTPUT.PUT_LINE(123);
END;
FUNCTION F1 RETURN VARCHAR2
IS

BEGIN
  RETURN 'string';
END;
END;

The last demonstration is used only to show how pkwrap_forward_table and pkwrap_reverse_table are used during Oracle Wrap process.
It has no practical meaning apart from verifying an assumption about how Oracle code uses these tables.
It is also an example of how behavior of Oracle can be changed without having access to source code by way of patching binary files used by Oracle utilities.

Unwrapping PL/SQL: a Systematic Approach

The high-level steps of how PL/SQL code appears to be wrapped since 10g on are below:

1. The source code is normalized (converted to upper-case, comments are removed, etc.)
2. The normalized code is compressed using zip
3. The compressed byte stream is obfuscated using a substitution table
4. Finally, Base64 encoding is applied

If we want to rewind that process and get the unwrapped code, the best we can do is to get the normalized code.
Steps 4 (Base64 Encoding) and 2 (ZIP Compression) are reversible, however, the obfuscation step 3 supposedly uses a substitution table which is the secret sauce of Oracle Wrap process.
It is known how to obtain that substitution table using a kind of a brute-force approach, as it was demonstrated in the following links:

• Unwrapping Oracle PLSQL
• Unwrapping 10G wrapped PL/SQL by Anton Scheffer

Once the substitution table is known, it becomes feasible to write a fully-fledged unwrapper. See for example
Unwrapping Oracle PL/SQL with unwrap.py by Niels Teusink

In this blog post, I will show how I discovered that Wrap substitution table by Reverse Engineering Oracle binaries.

I will be using that code that Anton Scheffer referred to in his blog post, namely I need to be able to compress and decompress data in SQL:

create or replace java source named MY_COMPRESS
as
import java.io.*;
import java.util.zip.*;

public class MY_COMPRESS
{
  public static String Inflate( byte[] src )
  {
    try
    {
      ByteArrayInputStream bis = new ByteArrayInputStream( src );
      InflaterInputStream iis = new InflaterInputStream( bis );
      StringBuffer sb = new StringBuffer();
      for( int c = iis.read(); c != -1; c = iis.read() )
      {
        sb.append( (char) c );
      }
      return sb.toString();
    } catch ( Exception e )
    {
    }
    return null;
  }
  public static byte[] Deflate( String src, int quality )
  {
    try
    {
      byte[] tmp = new byte[ src.length() + 100 ];
      Deflater defl = new Deflater( quality );
      defl.setInput( src.getBytes( "UTF-8" ) );
      defl.finish();
      int cnt = defl.deflate( tmp );
      byte[] res = new byte[ cnt ];
      for( int i = 0; i < cnt; i++ )
        res[i] = tmp[i];
      return res;
    } catch ( Exception e )
    {
    }
    return null;
  }
}
/

alter java source MY_COMPRESS compile
/

create or replace package mycompress
is
  function deflate( src in varchar2 )
  return raw;
--
  function deflate( src in varchar2, quality in number )
  return raw;
--
  function inflate( src in raw )
  return varchar2;
--
end;
/

create or replace package body mycompress
is
  function deflate( src in varchar2 )
  return raw
  is
  begin
    return deflate( src, 6 );
  end;
--
  function deflate( src in varchar2, quality in number )
  return raw
  as language java
  name 'MY_COMPRESS.Deflate( java.lang.String, int ) return byte[]';
--
  function inflate( src in raw )
  return varchar2
  as language java
  name 'MY_COMPRESS.Inflate( byte[] ) return java.lang.String';
--
end;
/

Let's now obtain some data for analysis that will be used throughout this post:

SQL> with src as (
  2    select 'FUNCTION F RETURN NUMBER IS BEGIN RETURN 1; END;' txt
  3      from dual),
  4  wrap as (
  5    select src.txt,
  6           dbms_ddl.wrap( 'CREATE OR REPLACE ' || src.txt) wrap
  7      from src),
  8  subst as (
  9    select substr(utl_encode.base64_decode(utl_raw.cast_to_raw(rtrim(substr(wrap.wrap, instr(wrap.wrap,
 chr(10), 1, 20) + 1), chr(10)))), 41) x,
 10           mycompress.deflate(wrap.txt||chr(0)) d
 11      from wrap)
 12  select to_number(substr(x, r*2 -1,2), 'xx') wrapped,
 13         substr(x, r*2 -1, 2) wrapped_hex,
 14         to_number(substr(d, r*2 -1,2), 'xx') zipped,
 15         substr(d, r*2 -1, 2) zipped_hex
 16    from subst,
 17         (select rownum r from dual connect by rownum <= 10);

   WRAPPED WRAPPED_HEX     ZIPPED ZIPPED_HEX
---------- ----------- ---------- -----------
        48 30                 120 78
       131 83                 156 9C
       199 C7                 115 73
       153 99                  11 0B
       129 81                 245 F5
       199 C7                 115 73
       203 CB                  14 0E
         8 08                 241 F1
       210 D2                 244 F4
       254 FE                 247 F7

The ZIPPED_HEX column shows compressed bytes whereas WRAPPED_HEX shows the corresponding wrapped byte. A simple brute-force approach can be used to obtain the reverse wrap substitution table to convert a wrapped byte to its corresponding zipped byte. Then, it is just enough to unzip the final byte stream to complete the unwrap exercise.

I was curious how to do the same by using a more systematic approach. For that, I was recording the function calls using DebugTrace from the Intel Pin Tools of a session wrapping PL/SQL code. I came across the pkwrap_obfuscate_source function:

pkwrap_obfuscate_source(0x7feb38878af8, 0x9, ...)
> bam_init(0x7feb38878af8, 0x7ffd567e9d18, ...)
| > kghalp(0x7feb3e4a69a0, 0x7feb3e4abe00, ...)
| | > kghprmalo(0x7feb3e4a69a0, 0, ...)
| | | > kghtshrt(0x7feb3e4a69a0, 0, ...)
| | | < kghtshrt+0x000000000169 returns: 0x40b38f0000000139
| | | > kghfnd_in_free_lists(0x7feb3e4a69a0, 0, ...)
| | | < kghfnd_in_free_lists+0x0000000001d5 returns: 0
| | | > kghfnd(0x7feb3e4a69a0, 0, ...)
| | | | > kghgex(0x7feb3e4a69a0, 0, ...)
| | | | | > kghalo(0x7feb3e4a69a0, 0x7feb3e4ad700, ...)
| | | | | | > kghfnd_in_free_lists(0x7feb3e4a69a0, 0, ...)
| | | | | | < kghfnd_in_free_lists+0x0000000001d5 returns: 0x7feb3e37bc00
| | | | | | > kghbshrt(0x7feb3e4a69a0, 0, ...)
| | | | | | < kghbshrt+0x000000000130 returns: 0x7feb3e37dbc8
| | | | | | > ksmpga_allo_cb(0x7feb3e4a69a0, 0x7feb3e4ad700, ...)
| | | | | | | > ksm_near_pga_limit_pdb(0x1, 0x3, ...)
| | | | | | | < ksm_near_pga_limit_pdb+0x00000000017f returns: 0
| | | | | | < ksmpga_allo_cb+0x0000000006a4 returns: 0
| | | | | < kghalo+0x000000000722 returns: 0x7feb3e37dbe0
| | | | | > kghaddex(0x7feb3e4a69a0, 0, ...)
| | | | | < kghaddex+0x00000000026a returns: 0x7feb3e37dbf0
| | | | < kghgex+0x00000000034e returns: 0x7feb3e37dbf0
| | | < kghfnd+0x00000000018b returns: 0x7feb3e37dbf0
| | < kghprmalo+0x00000000045e returns: 0x7feb3e37dc18
| | > _intel_fast_memset(0x7feb3e37dc18, 0, ...)
| | <> _intel_fast_memset.J(0x7feb3e37dc18, 0, ...)
| | <> __intel_memset(0x7feb3e37dc18, 0, ...)
| | < __intel_memset+0x000000000818 returns: 0x7feb3e37dc18
| < kghalp+0x0000000002d9 returns: 0x7feb3e37dc18
< bam_init+0x000000000105 returns: 0x7ffd567e9d18

It is coming from the pkwrap.o library within the $ORACLE_HOME/lib/libpls12.a archive. There's one especially interesting line in that file:

$ objdump -dzr pkwrap.o

    4ecd:       ff c3                   inc    %ebx
    4ecf:       89 d8                   mov    %ebx,%eax
    4ed1:       83 fb 14                cmp    $0x14,%ebx
    4ed4:       72 de                   jb     4eb4 <pkwrap_obfuscate_source+0x194>
    4ed6:       48 8b 55 c8             mov    -0x38(%rbp),%rdx
    4eda:       33 c9                   xor    %ecx,%ecx
    4edc:       48 8b 45 e0             mov    -0x20(%rbp),%rax
    4ee0:       48 85 c0                test   %rax,%rax
    4ee3:       48 89 55 d0             mov    %rdx,-0x30(%rbp)
    4ee7:       76 27                   jbe    4f10 <pkwrap_obfuscate_source+0x1f0>
    4ee9:       0f b6 04 0a             movzbl (%rdx,%rcx,1),%eax
    4eed:       48 83 c0 a0             add    $0xffffffffffffffa0,%rax
    4ef1:       0f b6 d8                movzbl %al,%ebx
    4ef4:       44 8a 83 00 00 00 00    mov    0x0(%rbx),%r8b
                        4ef7: R_X86_64_32S      pkwrap_forward_table

R_X86_64_32S is one of relocation types as it is defined in System V Application Binary Interface. Here's how to get that table:

$ readelf --syms pkwrap.o

Symbol table '.symtab' contains 94 entries:
   Num:    Value          Size Type    Bind   Vis      Ndx Name
     0: 0000000000000000     0 NOTYPE  LOCAL  DEFAULT  UND
     1: 0000000000000000     0 FILE    LOCAL  DEFAULT  ABS pkwrap.c
     2: 0000000000000000     0 SECTION LOCAL  DEFAULT    2
     3: 0000000000000000     0 SECTION LOCAL  DEFAULT    3
     4: 0000000000000000     0 SECTION LOCAL  DEFAULT    4
     5: 0000000000000000     0 SECTION LOCAL  DEFAULT    5
     6: 0000000000000000     0 SECTION LOCAL  DEFAULT    6
     7: 0000000000000010    80 FUNC    LOCAL  DEFAULT    6 pkwrap_skip_white_space
     8: 0000000000000060   304 FUNC    LOCAL  DEFAULT    6 pkwrap_read_hex
     9: 0000000000000000     0 SECTION LOCAL  DEFAULT    7
    10: 0000000000000190   272 FUNC    LOCAL  DEFAULT    6 pkwrap_read_hex_multi
    11: 00000000000002a0  3440 FUNC    LOCAL  DEFAULT    6 pkwrap_read_string
    12: 0000000000001010   896 FUNC    LOCAL  DEFAULT    6 pkwrap_read_string_paro
    13: 0000000000001390   192 FUNC    LOCAL  DEFAULT    6 pkwrap_read_number
    14: 0000000000001450   416 FUNC    LOCAL  DEFAULT    6 pkwrap_read_number_paro
    15: 00000000000015f0   400 FUNC    LOCAL  DEFAULT    6 pkwrap_2_to_4
    16: 0000000000001780   384 FUNC    LOCAL  DEFAULT    6 pkwrap_4_to_8
    17: 0000000000001900   976 FUNC    LOCAL  DEFAULT    6 pkwrap_read_ub_paro
    18: 0000000000001cd0   736 FUNC    LOCAL  DEFAULT    6 pkwrap_read_symtab
    19: 0000000000001fb0  1728 FUNC    LOCAL  DEFAULT    6 pkwrap_read_source
    20: 0000000000000000     0 SECTION LOCAL  DEFAULT    8
    21: 0000000000000080   256 OBJECT  LOCAL  DEFAULT    8 pkwrap_reverse_table
    22: 0000000000003cc0  4192 FUNC    LOCAL  DEFAULT    6 pkwrap_is_plsql
    23: 0000000000000000     0 SECTION LOCAL  DEFAULT    9
    24: 0000000000004d20  1072 FUNC    LOCAL  DEFAULT    6 pkwrap_obfuscate_source
    25: 0000000000000180   256 OBJECT  LOCAL  DEFAULT    8 pkwrap_forward_table

Those tables are coming from section 8 and start at addresses 0x00000080 (pkwrap_reverse_table) and 0x00000180 (pkwrap_forward_table). The aforementioned section can be seen as follows:

$readelf --hex-dump=8 pkwrap.o

Hex dump of section '.rodata':
 NOTE: This section has relocations against it, but these have NOT been applied to this dump.
  0x00000000 00000000 00000000 00000000 00000000 ................
  0x00000010 00000000 00000000 00000000 00000000 ................
  0x00000020 00000000 00000000 00000000 00000000 ................
  0x00000030 00000000 00000000 00000000 00000000 ................
  0x00000040 00000000 00000000 00000000 00000000 ................
  0x00000050 00000000 00000000 00000000 00000000 ................
  0x00000060 00000000 00000000 00000000 00000000 ................
  0x00000070 00000000 00000000 00000000 00000000 ................
  0x00000080 dd052553 b87b8227 91f24b03 eb5540ff ..%S.{.'..K..U@.
  0x00000090 1d081b3b c462c807 2a7e44c6 bea38bb7 ...;.b..*~D.....
  0x000000a0 0fd4de1a df72490a af89d5f6 bf51edb0 .....rI......Q..
  0x000000b0 18791596 5ce1a421 01a6994d 7675c91e .y..\..!...Mvu..
  0x000000c0 263e1985 a55a246c 0ec72e50 fd48933f &>...Z$l...P.H.?
  0x000000d0 70421158 f87dccd8 39ece8a7 f584f32c pB.X.}..9......,
  0x000000e0 e656cd45 4fd2c2e0 7cf06341 c52b3cb6 .V.EO...|.cA.+<.
  0x000000f0 00fc6f9d ac38bc74 d70ddcda d0880cd1 ..o..8.t........
  0x00000100 e795d37a e36883fe b9348c86 4335b480 ...z.h...4..C5..
  0x00000110 3d049af9 b565cf6a 5bab7f92 375faa16 =....e.j[...7_..
  0x00000120 54e9e4fa bd90a036 c1201fba 22d9ef61 T......6. .."..a
  0x00000130 4777ad71 789fb333 108efb8f 5ea95917 Gw.qx..3....^.Y.
  0x00000140 128752f4 57ca6713 3006c0ae f18d981c ..R.W.g.0.......
  0x00000150 2fce94b2 66cb236d 4c6bdb64 ee6009d6 /...f.#mLk.d.`..
  0x00000160 02a24e28 9c4ae2a8 46e5f773 3a5d81c3 ..N(.J..F..s:]..
  0x00000170 2d32eab1 29140b31 9b9e69a1 8abb976e -2..)..1..i....n
  0x00000180 7038e00b 9101c917 11de27f6 7e794820 p8........'.~yH
  0x00000190 b852c0c7 f5329fbf 30422312 cf103faa .R...2..0B#...?.
  0x000001a0 a937acd6 46024007 e3f4186d 5ff04ad0 .7..F.@....m_.J.
  0x000001b0 c8f7f1b7 898da79c 7558ec13 6e90414f ........uX..n.AO
  0x000001c0 0e6b518c 1a63e8b0 4d26e50a d83be264 .kQ..c..M&...;.d
  0x000001d0 4b2dc203 a00d61c4 53be4598 34edbc9d K-....a.S.E.4...
  0x000001e0 ddaf156a db95d4c6 85fa97d9 47d7ff72 ...j........G..r
  0x000001f0 50b325eb 773d3cb1 b4318305 6855199a P.%.w=<..1..hU..
  0x00000200 8fee0686 5d438bc1 7d29fc1e 8acdb9bb ....]C..})......
  0x00000210 a5089b4e d28133fe ce3a92f8 e473f9b5 ...N..3..:...s..
  0x00000220 a6fbe11d 3644395b e7bd9e99 74b2cb28 ....6D9[....t..(
  0x00000230 2ff3d3b6 8e946f1f 0488abfd 76a41c2c /.....o.....v..,
  0x00000240 caa866ef 146c1b49 163ec5d5 5662d196 ..f..l.I.>..Vb..
  0x00000250 7c7f6582 212adf78 57ad7bda 7a002224 |.e.!*.xW.{.z."$
  0x00000260 6735e684 a2e96080 5aa1f20c 592edcae g5....`.Z...Y...
  0x00000270 69cc095e c35c2bea 5493a3ba 714c870f i..^.\+.T...qL..
  0x00000280 9a999999 9999f13f 00000000 00002840 .......?......(@
  0x00000290 00000000 00003440 66666666 6666f63f ......4@ffffff.?
  0x000002a0 00000000 0000e043 0000005f 00000000 .......C..._....
  0x000002b0 00000000 00000000 00000000 00000000 ................

Judging by their names:

• pkwrap_forward_table should be applied at step 3 when the zipped byte stream is converted using the substitution table.
• pkwrap_reverse_table can be used to reverse that step.

Initially, I could not find out how both of those tables are used. Then, while I was studying the pkwrap_obfuscate_source procedure in Ghidra, I found a block of code that seemed quite promising:

if (CONCAT44(uStack36,local_28) != 0) {
    do {
      *(undefined *)(local_40 + uVar7) =
           pkwrap_forward_table[(ulong)(byte)(*(char *)(local_40 + uVar7) + 0xa0)];
      uVar7 = (ulong)((int)uVar7 + 1);
    } while (uVar7 < CONCAT44(uStack36,local_28));
  }

The important part here is how that table is accessed - there is 0xA0 which is 160 in decimal. Let's just try to lookup those zipped bytes against pkwrap_forward_table by adding that offset 0xA0 to them:

SQL> with src as (
  2    select 'FUNCTION F RETURN NUMBER IS BEGIN RETURN 1; END;' txt
  3      from dual),
  4  wrap as (
  5    select src.txt,
  6           dbms_ddl.wrap( 'CREATE OR REPLACE ' || src.txt) wrap
  7      from src),
  8  subst as (
  9    select substr(utl_encode.base64_decode(utl_raw.cast_to_raw(rtrim(substr(wrap.wrap, instr(wrap.wrap,
 chr(10), 1, 20) + 1), chr(10)))), 41) x,
 10           mycompress.deflate(wrap.txt||chr(0)) d
 11      from wrap)
 12  select to_number(substr(x, r*2 -1,2), 'xx') wrapped,
 13         substr(x, r*2 -1, 2) wrapped_hex,
 14         to_number(substr(d, r*2 -1,2), 'xx') zipped,
 15         substr(d, r*2 -1, 2) zipped_hex
 16    from subst,
 17         (select rownum r from dual connect by rownum <= 10);

   WRAPPED WRAPPED_HEX     ZIPPED ZIPPED_HEX
---------- ----------- ---------- -----------
        48 30                 120 78
       131 83                 156 9C
       199 C7                 115 73
       153 99                  11 0B
       129 81                 245 F5
       199 C7                 115 73
       203 CB                  14 0E
         8 08                 241 F1
       210 D2                 244 F4
       254 FE                 247 F7

I got the following table:

WRAPPED_HEX	ZIPPED_HEX	MOD(ZIPPED_HEX+0xA0,0x100)	PKWRAP_FORWARD_TABLE(MOD(ZIPPED_HEX+0xA0,0x100))
0x30	0x78	0x18	0x30
0x83	0x9C	0x3C	0x6E
0xC7	0x78	0x73	0xC7
0x99	0x0B	0xAB	0x99
0x81	0xF5	0x95	0x81
0xC7	0x78	0x73	0xC7
0xCB	0x0E	0xAE	0xCB
0x08	0xF1	0x91	0x08
0xD2	0xF4	0x94	0xD2
0xFE	0xF7	0x97	0xFE

Let me explain how the right most column is obtained. I will take the last row for this example. The wrapped byte is 0xFE, its corresponding zipped byte is 0xF7. MOD(0xF7 + 0xA0, 0x100)=0x97. The relevant entry from pkwrap_forward_table at index 0x97 is 0xFE (the same as the wrapped byte):

  0x00000180 7038e00b 9101c917 11de27f6 7e794820 p8........'.~yH
  0x00000190 b852c0c7 f5329fbf 30422312 cf103faa .R...2..0B#...?.
  0x000001a0 a937acd6 46024007 e3f4186d 5ff04ad0 .7..F.@....m_.J.
  0x000001b0 c8f7f1b7 898da79c 7558ec13 6e90414f ........uX..n.AO
  0x000001c0 0e6b518c 1a63e8b0 4d26e50a d83be264 .kQ..c..M&...;.d
  0x000001d0 4b2dc203 a00d61c4 53be4598 34edbc9d K-....a.S.E.4...
  0x000001e0 ddaf156a db95d4c6 85fa97d9 47d7ff72 ...j........G..r
  0x000001f0 50b325eb 773d3cb1 b4318305 6855199a P.%.w=<..1..hU..
  0x00000200 8fee0686 5d438bc1 7d29fc1e 8acdb9bb ....]C..})......
  0x00000210 a5089b4e d28133fe ce3a92f8 e473f9b5 ...N..3..:...s..
  0x00000220 a6fbe11d 3644395b e7bd9e99 74b2cb28 ....6D9[....t..(
  0x00000230 2ff3d3b6 8e946f1f 0488abfd 76a41c2c /.....o.....v..,
  0x00000240 caa866ef 146c1b49 163ec5d5 5662d196 ..f..l.I.>..Vb..
  0x00000250 7c7f6582 212adf78 57ad7bda 7a002224 |.e.!*.xW.{.z."$
  0x00000260 6735e684 a2e96080 5aa1f20c 592edcae g5....`.Z...Y...
  0x00000270 69cc095e c35c2bea 5493a3ba 714c870f i..^.\+.T...qL..

Note: I have no explanation right now why there is a discrepancy in the second line. As it has been shown pkwrap_forward_table can be used to convert compressed byte stream to wrapped bytes, it is now possible to obtain the reverse table that can be used to unwrap stored PL/SQL code (the secret sauce of the Oracle Wrap process that is known to be obtained using a brute-force algorithm):

SQL> declare
  2    v_forward_table raw(256) :=
  3      hextoraw('7038e00b9101c91711de27f67e794820'||
  4               'b852c0c7f5329fbf30422312cf103faa'||
  5               'a937acd646024007e3f4186d5ff04ad0'||
  6               'c8f7f1b7898da79c7558ec136e90414f'||
  7               '0e6b518c1a63e8b04d26e50ad83be264'||
  8               '4b2dc203a00d61c453be459834edbc9d'||
  9               'ddaf156adb95d4c685fa97d947d7ff72'||
 10               '50b325eb773d3cb1b43183056855199a'||
 11               '8fee06865d438bc17d29fc1e8acdb9bb'||
 12               'a5089b4ed28133fece3a92f8e473f9b5'||
 13               'a6fbe11d3644395be7bd9e9974b2cb28'||
 14               '2ff3d3b68e946f1f0488abfd76a41c2c'||
 15               'caa866ef146c1b49163ec5d55662d196'||
 16               '7c7f6582212adf7857ad7bda7a002224'||
 17               '6735e684a2e960805aa1f20c592edcae'||
 18               '69cc095ec35c2bea5493a3ba714c870f');
 19    type wrap_table_type is table of raw(1) index by pls_integer;
 20    v_reverse_table wrap_table_type;
 21    procedure populate_reverse_table
 22    is
 23      v_byte raw(1);
 24      v_index pls_integer;
 25    begin
 26      for i in 0..255
 27      loop
 28        v_index := i;
 29        v_byte := utl_raw.substr(v_forward_table, v_index + 1, 1);
 30        v_reverse_table(to_number(rawtohex(v_byte),'XX')):=hextoraw(to_char(mod(v_index - 160 + 256, 25
6), 'fm0X'));
 31      end loop;
 32    end populate_reverse_table;
 33    procedure print_reverse_table
 34    is
 35    begin
 36      for i in 0..255
 37      loop
 38        if mod(i,16)=0
 39        then
 40          dbms_output.new_line();
 41        end if;
 42        dbms_output.put(lower(rawtohex(v_reverse_table(i))));
 43      end loop;
 44      dbms_output.new_line();
 45    end print_reverse_table;
 46  begin
 47    populate_reverse_table();
 48    print_reverse_table();
 49  end;
 50  /
3d6585b318dbe287f152ab634bb5a05f
7d687b9b24c228678adea4261e03eb17
6f343e7a3fd2a96a0fe935561fb14d10
78d975f6bc4104816106f9add6d5297e
869e79e505ba84cc6e278eb05da8f39f
d0a271b858dd2c38994c480755e4538c
46b62da5af322240dc50c3a1258b9c16
605ccffd0c981cd4376d3c3a30e86c31
47f533da43c8e35e1994ece6a39514e0
9d64fa5915c52fcabb0bdff297bf0a76
b449445a1df0009621807f1a82394fc1
a7d70dd1d8ff139370ee5befbe09b977
72e7b254b72ac7739066200e51edf87c
8f2ef412c62b83cdaccb3bc44ec06936
6202ae88fcaa4208a64557d39abde123
8d924a1189746b91fbfec901ea1bf7ce

PL/SQL procedure successfully completed.

Now I can try to rewrap a sample piece of code using that substitution table:

SQL> with src as (
  2    select q'!package body test_pkg
  3  is
  4  procedure p1
  5  is
  6     v_local_var pls_integer := 10;
  7  begin
  8    dbms_output.put_line(123);
  9  end;
 10  function f1 return varchar2
 11  is
 12    /* code comment*/
 13  begin
 14    return 'string';
 15  end;
 16  end;!' txt
 17      from dual),
 18    wrap as (
 19      select dbms_ddl.wrap( 'create ' || src.txt ) wrap
 20        from src),
 21    base64_dcd as(
 22      select substr( utl_encode.base64_decode( utl_raw.cast_to_raw(rtrim( substr( wrap.wrap, instr( wra
p.wrap, chr( 10 ), 1, 20 ) + 1 ), chr(10) )  ) ), 41 ) x
 23        from wrap),
 24    subst as (
 25      select utl_raw.translate( x,
 26               hextoraw('000102030405060708090A0B0C0D0E0F' ||
 27                        '101112131415161718191A1B1C1D1E1F' ||
 28                        '202122232425262728292A2B2C2D2E2F' ||
 29                        '303132333435363738393A3B3C3D3E3F' ||
 30                        '404142434445464748494A4B4C4D4E4F' ||
 31                        '505152535455565758595A5B5C5D5E5F' ||
 32                        '606162636465666768696A6B6C6D6E6F' ||
 33                        '707172737475767778797A7B7C7D7E7F' ||
 34                        '808182838485868788898A8B8C8D8E8F' ||
 35                        '909192939495969798999A9B9C9D9E9F' ||
 36                        'A0A1A2A3A4A5A6A7A8A9AAABACADAEAF' ||
 37                        'B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF' ||
 38                        'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF' ||
 39                        'D0D1D2D3D4D5D6D7D8D9DADBDCDDDEDF' ||
 40                        'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF' ||
 41                        'F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF'),
 42               hextoraw('3D6585B318DBE287F152AB634BB5A05F' ||
 43                        '7D687B9B24C228678ADEA4261E03EB17' ||
 44                        '6F343E7A3FD2A96A0FE935561FB14D10' ||
 45                        '78D975F6BC4104816106F9ADD6D5297E' ||
 46                        '869E79E505BA84CC6E278EB05DA8F39F' ||
 47                        'D0A271B858DD2C38994C480755E4538C' ||
 48                        '46B62DA5AF322240DC50C3A1258B9C16' ||
 49                        '605CCFFD0C981CD4376D3C3A30E86C31' ||
 50                        '47F533DA43C8E35E1994ECE6A39514E0' ||
 51                        '9D64FA5915C52FCABB0BDFF297BF0A76' ||
 52                        'B449445A1DF0009621807F1A82394FC1' ||
 53                        'A7D70DD1D8FF139370EE5BEFBE09B977' ||
 54                        '72E7B254B72AC7739066200E51EDF87C' ||
 55                        '8F2EF412C62B83CDACCB3BC44EC06936' ||
 56                        '6202AE88FCAA4208A64557D39ABDE123' ||
 57                        '8D924A1189746B91FBFEC901EA1BF7CE')) s
 58      from base64_dcd)
 59  select mycompress.inflate( s ) unwrapped_code
 60    from subst
 61  /

UNWRAPPED_CODE
--------------------------------------------------------------------------------
PACKAGE BODY test_pkg
IS
PROCEDURE P1
IS
   V_LOCAL_VAR PLS_INTEGER := 10;
BEGIN
  DBMS_OUTPUT.PUT_LINE(123);
END;
FUNCTION F1 RETURN VARCHAR2
IS

BEGIN
  RETURN 'string';
END;
END;

Therefore, it has been demonstrated that the reverse table obtained with a help of pkwrap_forward_table can be used to unwrap this sample piece of PL/SQL code.

Going back to pkwrap_reverse_table, let's take a look at both pkwrap_reverse_table and my_reverse_table tables together to see if we can find any commonality among them: 1. pkwrap_reverse_table that we do not know how to use yet:

  dd052553 b87b8227 91f24b03 eb5540ff
  1d081b3b c462c807 2a7e44c6 bea38bb7
  0fd4de1a df72490a af89d5f6 bf51edb0
  18791596 5ce1a421 01a6994d 7675c91e
  263e1985 a55a246c 0ec72e50 fd48933f
  70421158 f87dccd8 39ece8a7 f584f32c
  e656cd45 4fd2c2e0 7cf06341 c52b3cb6
  00fc6f9d ac38bc74 d70ddcda d0880cd1
  e795d37a e36883fe b9348c86 4335b480
  3d049af9 b565cf6a 5bab7f92 375faa16
  54e9e4fa bd90a036 c1201fba 22d9ef61
  4777ad71 789fb333 108efb8f 5ea95917
  128752f4 57ca6713 3006c0ae f18d981c
  2fce94b2 66cb236d 4c6bdb64 ee6009d6
  02a24e28 9c4ae2a8 46e5f773 3a5d81c3
  2d32eab1 29140b31 9b9e69a1 8abb976e

2. the reverse table that I obtained running my PL/SQL code against pkwrap_forward_table:

  3d6585b3 18dbe287 f152ab63 4bb5a05f
  7d687b9b 24c22867 8adea426 1e03eb17
  6f343e7a 3fd2a96a 0fe93556 1fb14d10
  78d975f6 bc410481 6106f9ad d6d5297e
  869e79e5 05ba84cc 6e278eb0 5da8f39f
  d0a271b8 58dd2c38 994c4807 55e4538c
  46b62da5 af322240 dc50c3a1 258b9c16
  605ccffd 0c981cd4 376d3c3a 30e86c31
  47f533da 43c8e35e 1994ece6 a39514e0
  9d64fa59 15c52fca bb0bdff2 97bf0a76
  b449445a 1df00096 21807f1a 82394fc1
  a7d70dd1 d8ff1393 70ee5bef be09b977
  72e7b254 b72ac773 9066200e 51edf87c
  8f2ef412 c62b83cd accb3bc4 4ec06936
  6202ae88 fcaa4208 a64557d3 9abde123
  8d924a11 89746b91 fbfec901 ea1bf7ce

It can be easily spotted that the bytes in these tables differ from one another by a fixed offset 0x60 (96 in decimal). For example:

• 0xDD(pkwrap_reverse_table[0x00]) + 0x60 = 0x3D(the value of my_reverse_table[0x00])
• 0xB7(pkwrap_reverse_table[0x1F]) + 0x60 = 0x17(the value of my_reverse_table[0x1F])

Hence, that could be the way that reverse table is used within Oracle code (I know that it is used in the pkwrap_read_source function for sure).

References:

1. How to unwrap PL/SQL by Pete Finnigan
2. Unwrapping Oracle PLSQL
3. Unwrapping 10G wrapped PL/SQL by Anton Scheffer
4. Unwrapping Oracle PL/SQL with unwrap.py by Niels Teusink

v$db_pipes: Unveiling the Truth of Oracle Hash Value

A question about optimizing access to v$db_pipes just came up recently on the SQL.RU Oracle database forum. The Topic Starter wanted to find out if there is a way to speed up the query against v$db_pipes or there are any other options to see if a named pipe exists.
So here is the query that was run and its plan:

SQL> explain plan for
  2  select count(*)
  3    from v$db_pipes
  4   where name = 'MY_PIPE';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2656999297

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |   148 |     1 (100)| 00:00:01 |
|   1 |  SORT AGGREGATE   |         |     1 |   148 |            |          |
|*  2 |   FIXED TABLE FULL| X$KGLOB |     1 |   148 |     1 (100)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("KGLNAOBJ"='MY_PIPE' AND "KGLHDNSP"=7 AND "KGLOBSTA"<>0
              AND "INST_ID"=USERENV('INSTANCE'))

It is seen that v$db_pipes is built on top of x$kglob, which is a fixed table.
Obviously, doing a FULL TABLE SCAN against it is not the best thing to do, especially when we work with a large enough shared pool.
Fixed tables can have Oracle provided indexes and x$kglob has the following ones:

SQL> select index_number, column_name from v$indexed_fixed_column where table_name='X$KGLOB';

INDEX_NUMBER COLUMN_NAME
------------ ------------
           1 KGLNAHSH
           2 KGLOBT03

KGLOBT03 is not populated for pipes and it stores the SQL_ID of a statement being executed.
KGLNAHSH is a hash value and it seems to store last 4 bytes of KGLNAHSV which is a full hash value:

SQL> select kglnaobj, kglnahsv, kglnahsh, to_char(kglnahsh, 'fm0xxxxxxx') hex_hash, kglhdnsp from x$kglob where kglnaobj in ('MY_PIPE',  'MY_PIPE1', 'MY_PIPE2');

KGLNAOBJ                       KGLNAHSV                           KGLNAHSH HEX_HASH    KGLHDNSP
------------------------------ -------------------------------- ---------- --------- ----------
MY_PIPE2                       53e58fa645a35847070108600b3043ce  187712462 0b3043ce           7
MY_PIPE1                       1bb0749b381c19f0dd4d47413a125cc1  974281921 3a125cc1           7
MY_PIPE                        cdff652a7449f169da313e5685b962d8 2243519192 85b962d8           7

If I knew a way to calculate the hash value upfront, then I could query x$kglob passing the hash value as a filter to get an indexed access path:

SQL> explain plan for
  2  select count(*)
  3    from x$kglob
  4   where kglnaobj = 'MY_PIPE'
  5     and kglhdnsp = 7
  6     and kglobsta <> 0
  7     and inst_id = userenv('instance')
  8     and kglnahsh = 2243519192;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2109255596

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |     1 |   155 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE          |                 |     1 |   155 |            |          |
|*  2 |   FIXED TABLE FIXED INDEX| X$KGLOB (ind:1) |     1 |   155 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   2 - filter("KGLNAHSH"=2243519192 AND "KGLNAOBJ"='MY_PIPE' AND "KGLHDNSP"=7 AND
              "KGLOBSTA"<>0 AND "INST_ID"=USERENV('INSTANCE'))

I decided to try to find out how the hash value is calculated.
Firstly, I took the library cache dump:

Bucket: #=90840 Mutex=0x6cb2aa60(283467841536, 2, 0, 6)
  LibraryHandle:  Address=0x654cc068 Hash=85b962d8 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=CDB$ROOT.MY_PIPE
      FullHashValue=cdff652a7449f169da313e5685b962d8 Namespace=PIPE(07) Type=PIPE(18) ContainerId=1 ContainerUid=1 Identifier=0 OwnerIdn=2147483644
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x654cc118(0, 0, 0, 0) Mutex=0x654cc1b8(66, 8, 0, 6)
    Flags=RON/PN0/[10012001] Flags2=[0000]
    WaitersLists:
      Lock=0x654cc0f8[0x654cc0f8,0x654cc0f8]
      Pin=0x654cc0d8[0x654cc0d8,0x654cc0d8]
      LoadLock=0x654cc150[0x654cc150,0x654cc150]
    Timestamp:
    LibraryObject:  Address=0x65f9cb98 HeapMask=0000-0001-0001-0000 Flags=EXS/NRC[0400] Flags2=[0000] Flags3=[0000] PublicFlags=[0000]

Based on my knowledge of Oracle internals, I supposed that the hash value should be somehow derived from the name of the object and possibly other attributes of it.
Unfortunately, I was not able to find a proper formula looking into the library cache dump (I was close, in fact, as I was combined the object name with the namespace, however, a proper formula is hard or even near to impossible to come by).
When my first attempts to solve this conundrum failed miserably, I took a break and returned to that task later on.

That time I took more systematic approach and started by unwrapping the DBMS_PIPE package. I found out that the DBMS_PIPE.CREATE_PIPE function calls the CREATEPIPE function that has a widely used INTERFACE pragma, which roughly means that the function is mapped to another C function within the Oracle kernel. The function to which CREATEPIPE should be mapped should probably be called kkxpcre based on the excellent Dennis Yurichev blog post (of course, Dennis' post was written a while ago but Oracle has not given much attention to pipes for a while and I seriously doubt that they have changed that mapping since then). All of this knowledge appears to be useless at a glance, however, it laid the groundwork for the next step.

The next step of my research started when I was using the DebugTrace program from Intel Pintools. I ran DebugTrace against the server process in which I created a new pipe named MY_PIPE that has the hash value 2243519192 which is 0x85b962d8 in hex.
Here is the relevant output of DebugTrace which I reformatted for brevity (this is the output that I obtained from Oracle 19c, I also got a 12.2 output initially that had a few minor differencies):

Call 0x000000000f7775db $ORACLE_HOME/bin/oracle:kkxpcre+0x0000000002db -> 0x000000000b032100 $ORACLE_HOME/bin/oracle:kkxpcrep(0x7f0fd3e6fe80, 0x7, ...)
| Call 0x000000000b03225b $ORACLE_HOME/bin/oracle:kkxpcrep+0x00000000015b -> 0x000000001280c720 $ORACLE_HOME/bin/oracle:kglget(0x7f0fd3f889a0, 0x7fff3aea4d50, ...)
| | Call 0x000000001280c844 $ORACLE_HOME/bin/oracle:kglget+0x000000000124 -> 0x00000000128119f0 $ORACLE_HOME/bin/oracle:kglLock(0x7f0fd3f889a0, 0x7fff3aea4d50, ...)
| | | Call 0x0000000012811b4b $ORACLE_HOME/bin/oracle:kglLock+0x00000000015b -> 0x0000000012815280 $ORACLE_HOME/bin/oracle:kglComputeHash(0x7f0fd3f889a0, 0x7fff3aea4dd0, ...)
| | | Tailcall 0x000000001281528d $ORACLE_HOME/bin/oracle:kglComputeHash+0x00000000000d -> 0x00000000128152b0 $ORACLE_HOME/bin/oracle:kglComputeHash0(0x7f0fd3f889a0, 0x7fff3aea4dd0, ...)
| | | | Call 0x0000000012815399 $ORACLE_HOME/bin/oracle:kglComputeHash0+0x0000000000e9 -> 0x00000000127dca20 $ORACLE_HOME/bin/oracle:kggmd5Update(0x7fff3aea4580, 0x7f0fd3e6fe80, ...)
| | | | | Call 0x00000000127dcad5 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000b5 -> 0x0000000006b74060 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy(0x7fff3aea4588, 0x7f0fd3e6fe80, ...)
| | | | | Tailcall 0x0000000006b740bc $ORACLE_HOME/bin/oracle:_intel_fast_memcpy+0x00000000005c -> 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P(0x7fff3aea4588, 0x7f0fd3e6fe80, ...)
| | | | | Tailcall 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P -> 0x0000000006b814e0 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy(0x7fff3aea4588, 0x7f0fd3e6fe80, ...)
| | | | | Return 0x0000000006b83cfa $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy+0x00000000281a returns: 0x7fff3aea4588
| | | | Return 0x00000000127dcae8 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000c8 returns: 0x7fff3aea4588
| | | | Call 0x000000001281545e $ORACLE_HOME/bin/oracle:kglComputeHash0+0x0000000001ae -> 0x00000000127dca20 $ORACLE_HOME/bin/oracle:kggmd5Update(0x7fff3aea4580, 0x12976914, ...)
| | | | | Call 0x00000000127dcad5 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000b5 -> 0x0000000006b74060 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy(0x7fff3aea458f, 0x12976914, ...)
| | | | | Tailcall 0x0000000006b740bc $ORACLE_HOME/bin/oracle:_intel_fast_memcpy+0x00000000005c -> 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P(0x7fff3aea458f, 0x12976914, ...)
| | | | | Tailcall 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P -> 0x0000000006b814e0 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy(0x7fff3aea458f, 0x12976914, ...)
| | | | | Return 0x0000000006b83f95 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy+0x000000002ab5 returns: 0x7fff3aea458f
| | | | Return 0x00000000127dcae8 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000c8 returns: 0x7fff3aea458f
| | | | Call 0x000000001281546f $ORACLE_HOME/bin/oracle:kglComputeHash0+0x0000000001bf -> 0x00000000127dca20 $ORACLE_HOME/bin/oracle:kggmd5Update(0x7fff3aea4580, 0x7580764c, ...)
| | | | | Call 0x00000000127dcad5 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000b5 -> 0x0000000006b74060 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy(0x7fff3aea4590, 0x7580764c, ...)
| | | | | Tailcall 0x0000000006b740bc $ORACLE_HOME/bin/oracle:_intel_fast_memcpy+0x00000000005c -> 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P(0x7fff3aea4590, 0x7580764c, ...)
| | | | | Tailcall 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P -> 0x0000000006b814e0 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy(0x7fff3aea4590, 0x7580764c, ...)
| | | | | Return 0x0000000006b83c86 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy+0x0000000027a6 returns: 0x7fff3aea4590
| | | | Return 0x00000000127dcae8 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000c8 returns: 0x7fff3aea4590
| | | | Call 0x0000000012815415 $ORACLE_HOME/bin/oracle:kglComputeHash0+0x000000000165 -> 0x00000000127dca20 $ORACLE_HOME/bin/oracle:kggmd5Update(0x7fff3aea4580, 0x7fff3aea45f0, ...)
| | | | | Call 0x00000000127dcad5 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000b5 -> 0x0000000006b74060 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy(0x7fff3aea4598, 0x7fff3aea45f0, ...)
| | | | | Tailcall 0x0000000006b740bc $ORACLE_HOME/bin/oracle:_intel_fast_memcpy+0x00000000005c -> 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P(0x7fff3aea4598, 0x7fff3aea45f0, ...)
| | | | | Tailcall 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P -> 0x0000000006b814e0 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy(0x7fff3aea4598, 0x7fff3aea45f0, ...)
| | | | | Return 0x0000000006b83726 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy+0x000000002246 returns: 0x7fff3aea4598
| | | | Return 0x00000000127dcae8 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000c8 returns: 0x7fff3aea4598
| | | | Call 0x0000000012815423 $ORACLE_HOME/bin/oracle:kglComputeHash0+0x000000000173 -> 0x00000000127dc8b0 $ORACLE_HOME/bin/oracle:kggmd5Finish(0x7fff3aea4580, 0, ...)
| | | | | Call 0x00000000127dc927 $ORACLE_HOME/bin/oracle:kggmd5Finish+0x000000000077 -> 0x00000000127dca20 $ORACLE_HOME/bin/oracle:kggmd5Update(0x7fff3aea4580, 0x14aa6460, ...)
| | | | | | Call 0x00000000127dcad5 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000b5 -> 0x0000000006b74060 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy(0x7fff3aea459c, 0x14aa6460, ...)
| | | | | | Tailcall 0x0000000006b740bc $ORACLE_HOME/bin/oracle:_intel_fast_memcpy+0x00000000005c -> 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P(0x7fff3aea459c, 0x14aa6460, ...)
| | | | | | Tailcall 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P -> 0x0000000006b814e0 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy(0x7fff3aea459c, 0x14aa6460, ...)
| | | | | | Return 0x0000000006b83e38 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy+0x000000002958 returns: 0x7fff3aea459c
| | | | | Return 0x00000000127dcae8 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000c8 returns: 0x7fff3aea459c
| | | | | Call 0x00000000127dc938 $ORACLE_HOME/bin/oracle:kggmd5Finish+0x000000000088 -> 0x00000000127dca20 $ORACLE_HOME/bin/oracle:kggmd5Update(0x7fff3aea4580, 0x7fff3aea4550, ...)
| | | | | | Call 0x00000000127dcaa4 $ORACLE_HOME/bin/oracle:kggmd5Update+0x000000000084 -> 0x0000000006b74060 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy(0x7fff3aea45c0, 0x7fff3aea4550, ...)
| | | | | | Tailcall 0x0000000006b740bc $ORACLE_HOME/bin/oracle:_intel_fast_memcpy+0x00000000005c -> 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P(0x7fff3aea45c0, 0x7fff3aea4550, ...)
| | | | | | Tailcall 0x0000000006b74030 $ORACLE_HOME/bin/oracle:_intel_fast_memcpy.P -> 0x0000000006b814e0 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy(0x7fff3aea45c0, 0x7fff3aea4550, ...)
| | | | | | Return 0x0000000006b83c86 $ORACLE_HOME/bin/oracle:__intel_ssse3_rep_memcpy+0x0000000027a6 returns: 0x7fff3aea45c0
| | | | | | Call 0x00000000127dcab1 $ORACLE_HOME/bin/oracle:kggmd5Update+0x000000000091 -> 0x00000000127dcba0 $ORACLE_HOME/bin/oracle:kggmd5Process(0x7fff3aea4a70, 0x7fff3aea4588, ...)
| | | | | | Return 0x00000000127dd68d $ORACLE_HOME/bin/oracle:kggmd5Process+0x000000000aed returns: 0x66ba4229
| | | | | Return 0x00000000127dcae8 $ORACLE_HOME/bin/oracle:kggmd5Update+0x0000000000c8 returns: 0x66ba4229
| | | | Return 0x00000000127dc94a $ORACLE_HOME/bin/oracle:kggmd5Finish+0x00000000009a returns: 0x66ba4229
| | | Return 0x000000001281543b $ORACLE_HOME/bin/oracle:kglComputeHash0+0x00000000018b returns: 0x85b962d8

There are several important observations that can be made about this output:

• kkxpcre is a C function mapped to dbms_pipe.createpipe through the INTERFACE pragma which is called by dbms_pipe.create_pipe, which is the only function exposed to the public
• kglComputeHash0 returns that hash value 0x85b962d8 that I am looking for, hence I need to look carefully at what is going on between kkxpcre and kglComputeHash0 as the hash value is somewhere between
• The call stack is: kkxpcre->kkxpcrep->kglget->kglLock->kglComputeHash->kglComputeHash0, the latter calls kggmdUpdate multiple times, then it calls kggmd5Finish, which calls kggmd5Update twice. The last call to kggmd5Update also invokes kggmd5Process. In terms of Oracle code layers, the code can be split in the following ones:
  KKX(Programmatic interfaces to/from PL/SQL)->KGL(Library Cache)->KGG(Generic Routines)
• Based on the fact that we call MD5 functions, the underlying alrorithm is used MD5 somehow.

I have also disassembled kglComputeHash0 to find out how exactly kggmd5Update functions are called (this is the first call):

   0x0000000012815365 <+181>:   mov    %rax,-0x58(%rbp)
   0x0000000012815369 <+185>:   movl   $0x67452301,(%rax)
   0x000000001281536f <+191>:   mov    -0x58(%rbp),%rcx
   0x0000000012815373 <+195>:   movl   $0xefcdab89,0x4(%rcx)
   0x000000001281537a <+202>:   mov    -0x58(%rbp),%r8
   0x000000001281537e <+206>:   movl   $0x98badcfe,0x8(%r8)
   0x0000000012815386 <+214>:   mov    -0x58(%rbp),%r9
   0x000000001281538a <+218>:   movl   $0x10325476,0xc(%r9)
   0x0000000012815392 <+226>:   mov    0x10(%r14),%rsi
   0x0000000012815396 <+230>:   mov    (%r14),%edx
   0x0000000012815399 <+233>:   callq  0x127dca20 <kggmd5Update>

All of those constants: 0x67452301, 0xefcdab89, 0x98badcfe, 0x10325476 - are magic initialization constants in the MD5 algorithm.

Since the algorithm is known, in order to calculate a hash value, I need to know the input values to the MD5 functions.
Thus, I setup a few breakpoints in GDB and spent a while reviewing the register values.
Finally, I came up with the following GDB breakpoints:

set pagination off

break kggmd5Update
  commands
    printf "Length: %d\n",$rdx
    x/8xc $rsi
    c
  end

break kglComputeHash
  commands
    c
  end

break kggmd5Process
  commands
    c
  end

break kggmd5Finish
  commands
    c
  end

I attached to the server process through GDB and ran the following command in SQL*Plus:

SQL> var n number
SQL> exec :n:=dbms_pipe.create_pipe('MY_PIPE')

The debugger output showed this (comments are inline):

Breakpoint 5, 0x0000000012815280 in kglComputeHash ()

Breakpoint 4, 0x00000000127dca20 in kggmd5Update ()
Length: 7
0x7f7b9304ffe0: 77 'M'  89 'Y'  95 '_'  80 'P'  73 'I'  80 'P'  69 'E'  0 '\000'
-- MY_PIPE

Breakpoint 4, 0x00000000127dca20 in kggmd5Update ()
Length: 1
0x12976914:     46 '.'  0 '\000'        0 '\000'        0 '\000'        113 'q' 109 'm' 120 'x' 113 'q'
-- "." (just a dot)

Breakpoint 4, 0x00000000127dca20 in kggmd5Update ()
Length: 8
0x7580c61c:     67 'C'  68 'D'  66 'B'  36 '$'  82 'R'  79 'O'  79 'O'  84 'T'
-- CDB$ROOT

Breakpoint 4, 0x00000000127dca20 in kggmd5Update ()
Length: 4
0x7ffd3203e070: 7 '\a'  0 '\000'        0 '\000'        0 '\000'        0 '\000'        0 '\000'        0 '\000'  0 '\000'
-- 0x07000000 or chr(7)||chr(0)||chr(0)||chr(0)

Breakpoint 7, 0x00000000127dc8b0 in kggmd5Finish ()

Breakpoint 4, 0x00000000127dca20 in kggmd5Update ()
Length: 36
0x14aa6460 <kggmd5padding>:     -128 '\200'     0 '\000'        0 '\000'        0 '\000'        0 '\000'        0 '\000'   0 '\000'        0 '\000'

Breakpoint 4, 0x00000000127dca20 in kggmd5Update ()
Length: 8
0x7ffd3203dfd0: -96 '\240'      0 '\000'        0 '\000'        0 '\000'        0 '\000'        0 '\000'        0 '\000'   0 '\000'

Breakpoint 6, 0x00000000127dcba0 in kggmd5Process ()

Let me explain what this output means. In accordance with: System V ABI AMD64, the function parameters passed as follows (page 21):

• %rdi - first argument to functions
• %rsi - second argument to functions
• %rdx - third argument to functions

While I was looking at those registers, I produced a hypothesis that kggmd5Update has the signature similar to the one mentioned in RFC-1321: The MD5 Message-Digest Algorithm:

/* MD5 block update operation. Continues an MD5 message-digest
  operation, processing another message block, and updating the
  context.
 */
void MD5Update (context, input, inputLen)
MD5_CTX *context;                                        /* context */
unsigned char *input;                                /* input block */
unsigned int inputLen;                     /* length of input block */

As such, I was not really interested in the context variable - it holds the address of the context structure.
The second and third parameters, though, should hold the input string and its length, that is what I was getting in my GDB breakpoint (remember, that rsi and rdx are second and third parameters correspondingly):

break kggmd5Update
  commands
    printf "Length: %d\n",$rdx
    x/8xc $rsi
    c
  end

First three calls in the GDB output are self-explanatory: it is a string 'MY_PIPE.CDB$ROOT'. The last call requires a bit of explanation, though: what is 0x07000000? It seems to be the namespace, which is 7 for pipes (X$KGLOB.KGLHDNSP), with 3 trailing zero bytes.
I calculated MD5 hashes based on that formula and got this:

SQL> select dbms_crypto.hash(rawtohex('MY_PIPE1.CDB$ROOT'||chr(7)||chr(0)||chr(0)||chr(0)), 2) md5 from dual;

MD5
--------------------------------
9B74B01BF0191C3841474DDDC15C123A

SQL> select dbms_crypto.hash(rawtohex('MY_PIPE2.CDB$ROOT'||chr(7)||chr(0)||chr(0)||chr(0)), 2) md5 from dual;

MD5
--------------------------------
A68FE5534758A34560080107CE43300B

SQL> select dbms_crypto.hash(rawtohex('MY_PIPE.CDB$ROOT'||chr(7)||chr(0)||chr(0)||chr(0)), 2) md5 from dual;

MD5
--------------------------------
2A65FFCD69F14974563E31DAD862B985

Whereas the actual hashes were these:

SQL> select kglnaobj, kglnahsv, kglnahsh, to_char(kglnahsh, 'fm0xxxxxxx') hex_hash, kglhdnsp from x$kglob where kglnaobj in ('MY_PIPE',  'MY_PIPE1', 'MY_PIPE2');

KGLNAOBJ                       KGLNAHSV                           KGLNAHSH HEX_HASH    KGLHDNSP
------------------------------ -------------------------------- ---------- --------- ----------
MY_PIPE2                       53e58fa645a35847070108600b3043ce  187712462 0b3043ce           7
MY_PIPE1                       1bb0749b381c19f0dd4d47413a125cc1  974281921 3a125cc1           7
MY_PIPE                        cdff652a7449f169da313e5685b962d8 2243519192 85b962d8           7

Then it dawned on me that Oracle uses the same technique for these hashes as it uses for SQL_ID: Function to compute SQL_ID out of SQL_TEXT - it reverses the order of each 4 bytes. Look:

SQL> with pipes(pipe_name) as (
  2    select 'MY_PIPE' from dual union all
  3    select 'MY_PIPE1' from dual union all
  4    select 'MY_PIPE2' from dual
  5  )
  6  select pipe_name,
  7         utl_raw.concat(
  8           utl_raw.reverse(utl_raw.substr(hv, 1, 4)),
  9           utl_raw.reverse(utl_raw.substr(hv, 5, 4)),
 10           utl_raw.reverse(utl_raw.substr(hv, 9, 4)),
 11           utl_raw.reverse(utl_raw.substr(hv, 13, 4))
 12         ) ora_hash_value
 13    from (select pipe_name,
 14                 dbms_crypto.hash(rawtohex(pipe_name||'.CDB$ROOT'||chr(7)||chr(0)||chr(0)||chr(0)), 2) hv
 15            from pipes)
 16   order by pipe_name;

PIPE_NAM ORA_HASH_VALUE
-------- --------------------------------
MY_PIPE  CDFF652A7449F169DA313E5685B962D8
MY_PIPE1 1BB0749B381C19F0DD4D47413A125CC1
MY_PIPE2 53E58FA645A35847070108600B3043CE

These are exactly the values I was looking for!

tl;dr

• The pipe full hash value can be calculated as below (where ORA_HASH_VALUE is the hash that I calculated based on the research made in this article):
  

  SQL> select lower(rawtohex(utl_raw.concat(
    2           utl_raw.reverse(utl_raw.substr(hv, 1, 4)),
    3           utl_raw.reverse(utl_raw.substr(hv, 5, 4)),
    4           utl_raw.reverse(utl_raw.substr(hv, 9, 4)),
    5           utl_raw.reverse(utl_raw.substr(hv, 13, 4))
    6         ))) ora_hash_value,
    7         kglnahsv, kglnahsh, hex_hash, kglhdnsp, kglhdnsd, kglnacon, kglnaown, kglnaobj
    8    from (select dbms_crypto.hash(rawtohex(kglnaobj||'.'||kglnacon||chr(kglhdnsp)||chr(0)||chr(0)||chr(0)), 2) hv,
    9                 kglnahsv, kglnahsh, to_char(kglnahsh, 'fm0xxxxxxx') hex_hash,
   10                 kglhdnsp, kglhdnsd, kglnacon, kglnaown, kglnaobj
   11            from x$kglob t
   12           where kglhdnsp = 7)
   13   order by kglnaobj
   14  /
  
  ORA_HASH_VALUE                   KGLNAHSV                           KGLNAHSH HEX_HASH    KGLHDNSP KGLHDNSD KGLNACON KGLNAOWN KGLNAOBJ
  -------------------------------- -------------------------------- ---------- --------- ---------- -------- -------- -------- --------
  cdff652a7449f169da313e5685b962d8 cdff652a7449f169da313e5685b962d8 2243519192 85b962d8           7 PIPE     CDB$ROOT          MY_PIPE
  1bb0749b381c19f0dd4d47413a125cc1 1bb0749b381c19f0dd4d47413a125cc1  974281921 3a125cc1           7 PIPE     CDB$ROOT          MY_PIPE1
  53e58fa645a35847070108600b3043ce 53e58fa645a35847070108600b3043ce  187712462 0b3043ce           7 PIPE     CDB$ROOT          MY_PIPE2
  

  1. We take: "<PIPE_NAME>.<CONTAINER_NAME>chr(7)chr(0)chr(0)chr(0)".
  2. Compute an MD5 hash of it.
  3. Then the order of each 4 bytes is reversed and the final output is assembled.
  4. If you are interested in the numeric hash value, which is X$KGLOB.KGLNAHSH, you need to take last 4 bytes of the full hash value, which is X$KGLOB.KGLNAHSV.
  5. I do not consider the non-CDB architecture due to its deprecation but the general pattern should be applicable to it as well.
  
• Due to the fact that v$db_pipes does not expose the hash value, it's a good idea to create an extended version of that view including the hash value, since we now know how to calculate it (legal disclaimer: that's just my opinion). After all, Oracle has provided functions to calculate the SQL_ID. They might provide similar functions to calculate hash values one day.