[20241114]建立完善ext_kglob.sh脚本.txt

[20241114]建立完善ext_kglob.sh脚本.txt

--//以前考虑使用管道问题，我考虑复杂了，看了gdb文档，实际上gdb -ex参数支持在命令行加入执行命令。
--//选择使用mmon后台进程，改写如下：
$ cat ext_kglob.sh
#/bin/bash
# extrace object string from object handle address
# arg1=address arg2=offset (default 1c8) arg3=length (default 1)

offset=${2:-1c8}
length=${3:-1}
gdb -q -batch -p $(pgrep -f ora_mmon_${ORACLE_SID}) -ex "x/${length}s 0x${1}+0x${offset}" -ex "quit" |  grep "^0x" | grep -v "^0x0"

--//21c下偏移在0x1c8，11g下偏移在1a8，根据环境需要修改。
--//该工具仅仅建议在测试环境学习使用。
--//简单验证看看：
SCOTT@book01p> select * from dept where deptno=40;

    DEPTNO DNAME                          LOC
---------- ------------------------------ -------------
        40 OPERATIONS                     BOSTON

SCOTT@book01p> @ hash

HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
3912471479 14ymr4znm74xr            0     103351      2852011669  e93393b7  2024-11-14 09:18:39    16777216

SYS@book> @ sharepool/shp4p 14ymr4znm74xr 0
SYS@book> @ pr
==============================
HANDLE_TYPE                   : parent handle address
KGLHDADR                      : 0000000062B283D0
KGLHDPAR                      : 0000000062B283D0
C40                           : select * from dept where deptno=40
KGLHDLMD                      : 0
KGLHDPMD                      : 0
KGLHDIVC                      : 0
KGLOBHD0                      : 0000000062B28210
KGLOBHD6                      : 00
KGLOBHS0                      : 4064
KGLOBHS6                      : 0
KGLOBT16                      : 0
N0_6_16                       : 4064
N20                           : 4064
KGLNAHSH                      : 3912471479
KGLOBT03                      : 14ymr4znm74xr
KGLOBT09                      : 65535
PL/SQL procedure successfully completed.

$ ext_kglob.sh 0000000062B283D0
0x62b28598:     "select * from dept where deptno=40"

--//如果你不确定定位偏移或者不对，可以通过简单的方法执行确定：
$ ext_kglob.sh 0000000062B283D0 0 816 | grep -i select
0x62b28598:     "select * from dept where deptno=40"
--//21c下偏移在0x1c8，11g下偏移在1a8，根据环境需要修改。
--//0x62b28598-0x0000000062B283D0 = 0x1c8

--//看看表dept对象。
$ sql_idz.sh 'DEPT.SCOTT.BOOK01P\x1\0\0\0' 3
sql_text = DEPT.SCOTT.BOOK01P\x1\0\0\0
full_hash_value(16) = 05DB243908B3C797B99628590EDB820C or 05db243908b3c797b99628590edb820c
xxxxx_matching_signature(10) = -5073823567219817972 or  13372920506489733644
hash_value(10) = 249266700 or hash_value(16) = 0EDB820C or 0edb820c
sql_id(16) = B99628590EDB820C or b99628590edb820c
sql_id(32) = bm5j8b47dr0hc
sql_id(32) = bm5j8b47dr0hc
sql_id(32) = bm5j8b47dr0hc

SYS@book> @ sharepool/shp4p '' 249266700
SYS@book> @ pr
==============================
HANDLE_TYPE                   : parent handle address
KGLHDADR                      : 0000000062B02760
KGLHDPAR                      : 0000000062B02760
C40                           : DEPT.SCOTT
KGLHDLMD                      : 0
KGLHDPMD                      : 0
KGLHDIVC                      : 0
KGLOBHD0                      : 0000000062B02070
KGLOBHD6                      : 00
KGLOBHS0                      : 4064
KGLOBHS6                      : 0
KGLOBT16                      : 0
N0_6_16                       : 4064
N20                           : 4064
KGLNAHSH                      : 249266700
KGLOBT03                      :
KGLOBT09                      : 0
PL/SQL procedure successfully completed.

$ ext_kglob.sh 0000000062B02760
0x62b02928:     "DEPTSCOTTBOOK01P\030F\260b"
--//后面取到一些乱码是正常的。

--//顺便SQL测试中间出现\0会是怎么情况。

$ echo -e "select 'A\0' from dual;" >| gg1.txt
$ xxd -c 16  gg1.txt
0000000: 7365 6c65 6374 2027 4100 2720 6672 6f6d  select 'A.' from
0000010: 2064 7561 6c3b 0a                         dual;.

SCOTT@book01p> set echo on
SCOTT@book01p> @ gg1.txt;
SCOTT@book01p> select 'A
  2  ;
ERROR:
ORA-01756: quoted string not properly terminated
--//sqlplus在读取到chr(0)时中断了，变成不正确的sql语句，无法测试。

$ ext_kglob.sh 00000000623AF968
0x623afb30:     "select 'A\001' from dual"
--//换成\01的测试结果。