[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)"
--//使用该脚本计算存在小问题,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)"
