[20240512]建立完善sql_idz.sh脚本.txt

[20240512]建立完善sql_idz.sh脚本.txt --//使用该脚本计算存在小问题,cat 文件中如果有ascii=0,cat时会过滤掉,一般table namespac是1. --//在尾部追加\01\0\0\0,而sql语句追加的是\0.我以前代码写死的.修改更加灵活一些. --//增加参数3,如果不输入缺省等于\0. --//增加计算exact_matching_signature, force_matching_signature值,当然文本格式要经过格式化输入正确才行. --//当参数2 = 3时 ,计算exact_matching_signature, force_matching_signature --//顺便做了小量改动. --//测试看看: SYS@test> select * from v$db_object_cache where name = 'DEPT'   2   @prxx ============================== OWNER                         : SCOTT NAME                          : DEPT DB_LINK                       : NAMESPACE                     : TABLE/PROCEDURE TYPE                          : TABLE SHARABLE_MEM                  : 4072 LOADS                         : 1 EXECUTIONS                    : 0 LOCKS                         : 0 PINS                          : 0 KEPT                          : NO CHILD_LATCH                   : 93026 INVALIDATIONS                 : 0 HASH_VALUE                    : 2956815202 LOCK_MODE                     : NONE PIN_MODE                      : NONE STATUS                        : VALID TIMESTAMP                     : 2019-08-17/21:38:48 PREVIOUS_TIMESTAMP            : LOCKED_TOTAL                  : 2 PINNED_TOTAL                  : 2 PROPERTY                      : FULL_HASH_VALUE               : d228ecb73e713f600e1f246bb03d6b62 CON_ID                        : 3 CON_NAME                      : TEST01P ADDR                          : 000007FF002CD2E0 EDITION                       : PL/SQL procedure successfully completed. $ ./sql_idz.sh  'DEPT.SCOTT.TEST01P'  0 "\01\0\0\0" sql_text = DEPT.SCOTT.TEST01P\01\0\0\0 full_hash_value(16) = D228ECB73E713F600E1F246BB03D6B62 hash_value(10) = 2956815202 sql_id(32) = 0w7t4dfs3uuv2 sql_id(32) = 0w7t4dfs3uuv2 sql_id(32) = 0w7t4dfs3uuv2 --//注意:表的full_hash_value格式是 table.owner.pdbname\01\0\0\0. --///如果11g,没有.pdbname. $ ./sql_idz.sh  'DEPT.SCOTT.TEST01P\01\0\0'  0 sql_text = DEPT.SCOTT.TEST01P\01\0\0\0 full_hash_value(16) = D228ECB73E713F600E1F246BB03D6B62 or d228ecb73e713f600e1f246bb03d6b62 hash_value(10) = 2956815202 sql_id(32) = 0w7t4dfs3uuv2 sql_id(32) = 0w7t4dfs3uuv2 sql_id(32) = 0w7t4dfs3uuv2 $ ./sql_idz.sh  'DEPT.SCOTT.TEST01P\01\0\0\0'  3 sql_text = DEPT.SCOTT.TEST01P\01\0\0\0 full_hash_value(16) = D228ECB73E713F600E1F246BB03D6B62 or d228ecb73e713f600e1f246bb03d6b62 xxxxx_matching_signature(10) = 1017572085745937250 hash_value(10) = 2956815202 sql_id(32) = 0w7t4dfs3uuv2 sql_id(32) = 0w7t4dfs3uuv2 sql_id(32) = 0w7t4dfs3uuv2 --//以上方法计算都正确. --//看看sql语句: SYS@test> select sysdate from dual ; SYSDATE ------------------- 2024-05-12 20:47:25 --//注意分号前有1个空格. SYS@test> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------  409144692 f0wzs9nc663bn            0      68980      1388734953  18630d74  2024-05-12 20:47:25    16777216 $ ./sql_idz.sh  'select sysdate from dual '  0 sql_text = select sysdate from dual \0 full_hash_value(16) = 2F36775C951D24FEE073F84D18630D74 hash_value(10) = 409144692 sql_id(32) = f0wzs9nc663bn sql_id(32) = f0wzs9nc663bn sql_id(32) = f0wzs9nc663bn --//409144692%2^17 = 68980 --//完全能对上!! --//测试exact_matching_signature, force_matching_signature看看. SCOTT@test01p> select 1 from dual ;          1 ----------          1 SCOTT@test01p> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 4041728556 054n8y7sfgsjc            0     123436      1388734953  f0e7e22c  2024-05-15 20:41:03    16777216 SCOTT@test01p> select sql_text,exact_matching_signature, force_matching_signature from v$sqlarea where sql_id='054n8y7sfgsjc'; SQL_TEXT           EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE ------------------ ------------------------ ------------------------ select 1 from dual     12518811395313535686     10559245208183986822 --//EXACT_MATCHING_SIGNATURE=12518811395313535686, FORCE_MATCHING_SIGNATURE=10559245208183986822. $ sql_idz.sh  'SELECT 1 FROM DUAL'  3 sql_text = SELECT 1 FROM DUAL full_hash_value(16) = D9F6DF623A086C51ADBBC0A2F3C68AC6 or d9f6df623a086c51adbbc0a2f3c68ac6 xxxxx_matching_signature(10) = -5927932678396015930 hash_value(10) = 4089875142 sql_id(32) = avfy0nbtwd2q6 sql_id(32) = avfy0nbtwd2q6 sql_id(32) = avfy0nbtwd2q6 --//注意要统一格式化,小写变成大写,dual后面没有空格. --//超出范围了. --//2^64-5927932678396015930 = 12518811395313535686 正好对上!! $ sql_idz.sh  'SELECT :"SYS_B_0" FROM DUAL' 3 sql_text = SELECT :"SYS_B_0" FROM DUAL full_hash_value(16) = A995B29240A442869289F992520D5A86 or a995b29240a442869289f992520d5a86 xxxxx_matching_signature(10) = -7887498865525564794 hash_value(10) = 1376606854 sql_id(32) = 952gtk990uqn6 sql_id(32) = 952gtk990uqn6 sql_id(32) = 952gtk990uqn6 --//超出范围了. --//2^64 -7887498865525564794 = 10559245208183986822 --//再次改写,我有点懒,如果xxxxx_matching_signature那行第1个是负数取最后1个对应matching_signature. $ sql_idz.sh  'SELECT :"SYS_B_0" FROM DUAL' 3 sql_text = SELECT :"SYS_B_0" FROM DUAL full_hash_value(16) = A995B29240A442869289F992520D5A86 or a995b29240a442869289f992520d5a86 xxxxx_matching_signature(10) = -7887498865525564794 or  10559245208183986822 hash_value(10) = 1376606854 sql_id(32) = 952gtk990uqn6 sql_id(32) = 952gtk990uqn6 sql_id(32) = 952gtk990uqn6 --//OK --//sql_idz.sql,注意脚本里面的^M在insert模式下按ctrl+q,ctrl+M输入(windows版本),ctrl+v,ctrl+M输入(linux版本) #! /bin/bash # calcucate sql_text of full_hash_value(16),hash_value(10),sql_id(32). # argv1 sql statement or sql of text file # argv2 flag: 0= sql statement 1=sql of text file for sqlplus  2=sql of text file for other 3=original # argv3 default = '\0' add tailstr odebug=${ODEBUG:-0} oflag=${2:-0} tailstr=${3:-'\0'} if [ $oflag -eq 0 ] then     sql_text=${1}${tailstr} fi # sqlplus format sql_text if [ $oflag -eq 1 ] then sql_text="$( cat $1 | sed -e "s/ $//" -e "s/\s*$//" -e '$s/;$//')""${tailstr}" # sql_text="$( cat $1 | unix2dos | sed '$s/;\s*$//')"'\0' # sql_text="$( cat $1 | sed -e "s/ $//" -e sed '$s/;\s*$//')"'\0' # sql_text="$( cat $1 | sed '$s/;\s*$//')"'\0' fi # other format sql_text if [ $oflag -eq 2 ] then sql_text="$( cat $1 | sed '$s/;\s*$//')""${tailstr}" # sql_text="$( cat $1 | unix2dos | sed '$s/;\s*$//')"'\0' # sql_text="$( cat $1 | sed -e "s/ $//" -e '$s/;\s*$//')"'\0' # sql_text="$( cat $1 | sed '$s/;\s*//')"'\0' fi # exact_matching_signature, force_matching_signature if [ $oflag -eq 3 ] then     sql_text=${1} fi v1=$(echo -e -n "$sql_text" | md5sum | sed 's/  -//' | xxd -r -p | od -t x4 |  sed   -n  -e 's/^0\+ //' -e 's/ //gp' | tr 'a-z' 'A-Z') v2=${v1:(-16):16} v3=${v2:(-8):8} # v2=$(echo "obase=16;ibase=16; $v1 % 10000000000000000" | bc| tr -d '\\\r\n') # v3=$(echo "obase=10;ibase=16; $v1 % 100000000" | bc| tr -d '\\\r\n') if [ $odebug -eq 1 ] ; then         echo v1=$v1 v2=$v2 v3=$v3 fi echo "sql_text = $sql_text" echo "full_hash_value(16) = $v1 or ${v1,,}" if [ $oflag -eq 3 ] ; then     echo "xxxxx_matching_signature(10) = $(( 16#$v2 )) or " $(echo $(( 16#$v2 )) + 2^64|bc ) fi echo "hash_value(10) = $(( 16#$v3 )) " BASE32=($(echo {0..9} {a..z} | tr -d 'eilo')) res='' for i in $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n') do         res=${res}${BASE32[$(( 10#$i ))]} done echo "sql_id(32) = $(printf "%13s" $res | tr ' ' '0')" echo "sql_id(32) = $(printf "%013s" $res)" res1=$(eval $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n' | awk 'BEGIN{RS=" +"; printf "echo " }/./{printf "${BASE32[$(( 10#%02d))]}", $1}' )) echo "sql_id(32) = $(printf "%013s" $res1)"