[20220104]文件格式与sql_id计算.txt

[20220104]文件格式与sql_id计算.txt --//节前测试，我发现v$sqlarea字段的sql_fulltext里面包括回车换行字符，我以前测试 --//在sqlplus下会转换删除chr(13) 链接http://blog.itpub.net/267265/viewspace-2214629/=>[20180918]文件格式与sql_id.txt --//很明显w3wp.exe模块执行的sql语句不做这样的处理。 --//可以通过生产系统的例子验证： 1.环境： xxxx1> @ prxx ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 19.0.0.0.0 BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0 BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试： $ cat sql_id.sql SET LINESIZE 32767 --SET LINESIZE 4000 VAR V_SQL_FULLTEXT CLOB COL SQL_FULLTEXT FOR A4000 WORD_WRAP SET FEEDBACK OFF SET SERVEROUTPUT ON PROMPT PROMPT --SQL_ID = &&1 PROMPT DECLARE     V_SQL_FULLTEXT   CLOB;     V_COUNT          NUMBER; BEGIN     SELECT COUNT(*) INTO V_COUNT  FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM=1;     IF  V_COUNT=1     THEN         --SELECT REPLACE (SQL_FULLTEXT||';', CHR(13), '') SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM = 1;         SELECT REPLACE (SQL_FULLTEXT||';', CHR(13), chr(13)) SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM = 1;         DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);     ELSE         SELECT COUNT(*)  INTO V_COUNT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;         IF  V_COUNT=1         THEN             --SELECT REPLACE (SQL_TEXT||';',CHR(13),'')  INTO V_SQL_FULLTEXT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;             SELECT REPLACE (SQL_TEXT||';',CHR(13),chr(13))  INTO V_SQL_FULLTEXT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;             DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);         END IF;     END IF;     EXCEPTION WHEN NO_DATA_FOUND THEN         NULL; END; / PROMPT SET SERVEROUTPUT OFF SET FEEDBACK 6 SET LINESIZE 277 --//注意我注解了SELECT REPLACE (SQL_FULLTEXT||';', CHR(13), '')，写成SELECT REPLACE (SQL_FULLTEXT||';', CHR(13), chr（13)，等于不替换。 xxxx1> spool aa.txt xxxx1> @sql_id fx0tght3t3436 --SQL_ID = fx0tght3t3436 SELECT distinct (to_char(GHYY_HYXX.GZRQ, 'yyyy-mm-dd')) GZRQ,                                     MS_GHKS.KSMC,                                     GHYY_HYXX.GHKS,                                     GY_YGDM.YGXM,                                     GHYY_HYXX.YSDM,                                     GHYY_HYXX.ZBLB,                                     GHYY_HYXX.TGBZ,                                     GY_DMZD.DMMC,                                     MS_MZGHF.HLMC,                                     MS_MZGHF.ZLF                       FROM GHYY_HYXX, MS_GHKS, GY_YGDM, MS_YSKS, GY_DMZD, MS_MZGHF                      WHERE GHYY_HYXX.GHKS = MS_GHKS.KSDM                        AND GHYY_HYXX.YSDM = GY_YGDM.YGDM                        AND MS_YSKS.Mzks = GHYY_HYXX.GHKS                        AND MS_YSKS.YSDM = GY_YGDM.YGDM                        AND MS_GHKS.KSDM NOT IN ('3005','3021','3022','3023', '3004')                        AND MS_YSKS.FYXH = MS_MZGHF.FYXH                        AND GHYY_HYXX.TGBZ = 0                        AND GY_DMZD.DMLB = 27                        AND GY_DMZD.DMSB <> 0                        AND GHYY_HYXX.GZRQ > SYSDATE - 1                        AND GHYY_HYXX.GZRQ <= (to_date(SYSDATE) + 7 + 3 / 24)                        AND GY_DMZD.DMSB = GY_YGDM.YGJB  order by YSDM, GZRQ; xxxx1> spool off $ cat -v aa.txt xxxx1> @sql_id fx0tght3t3436 --SQL_ID = fx0tght3t3436 SELECT distinct (to_char(GHYY_HYXX.GZRQ, 'yyyy-mm-dd')) GZRQ,^M                                     MS_GHKS.KSMC,^M                                     GHYY_HYXX.GHKS,^M                                     GY_YGDM.YGXM,^M                                     GHYY_HYXX.YSDM,^M                                     GHYY_HYXX.ZBLB,^M                                     GHYY_HYXX.TGBZ,^M                                     GY_DMZD.DMMC,^M                                     MS_MZGHF.HLMC,^M                                     MS_MZGHF.ZLF^M                       FROM GHYY_HYXX, MS_GHKS, GY_YGDM, MS_YSKS, GY_DMZD, MS_MZGHF^M                      WHERE GHYY_HYXX.GHKS = MS_GHKS.KSDM^M                        AND GHYY_HYXX.YSDM = GY_YGDM.YGDM^M                        AND MS_YSKS.Mzks = GHYY_HYXX.GHKS^M                        AND MS_YSKS.YSDM = GY_YGDM.YGDM^M                        AND MS_GHKS.KSDM NOT IN ('3005','3021','3022','3023', '3004')^M                        AND MS_YSKS.FYXH = MS_MZGHF.FYXH^M                        AND GHYY_HYXX.TGBZ = 0^M                        AND GY_DMZD.DMLB = 27^M                        AND GY_DMZD.DMSB <> 0^M                        AND GHYY_HYXX.GZRQ > SYSDATE - 1^M                        AND GHYY_HYXX.GZRQ <= (to_date(SYSDATE) + 7 + 3 / 24)^M                        AND GY_DMZD.DMSB = GY_YGDM.YGJB  order by YSDM, GZRQ; xxxx1> spool off --//你可以发现语句后面全部带有^M字符。 --//可以拿我以前写的计算sql_id的脚本sql_idx.sh验证看看。 --//编辑aa.txt文本，删除开头与结尾，注意结尾的分号也要删除，噢不删除也可以。 $ sql_idx.sh ~/hrp430/Testy/aa.txt 1 sql_text = SELECT distinct (to_char(GHYY_HYXX.GZRQ, 'yyyy-mm-dd')) GZRQ,                                     MS_GHKS.KSMC,                                     GHYY_HYXX.GHKS,                                     GY_YGDM.YGXM,                                     GHYY_HYXX.YSDM,                                     GHYY_HYXX.ZBLB,                                     GHYY_HYXX.TGBZ,                                     GY_DMZD.DMMC,                                     MS_MZGHF.HLMC,                                     MS_MZGHF.ZLF                       FROM GHYY_HYXX, MS_GHKS, GY_YGDM, MS_YSKS, GY_DMZD, MS_MZGHF                      WHERE GHYY_HYXX.GHKS = MS_GHKS.KSDM                        AND GHYY_HYXX.YSDM = GY_YGDM.YGDM                        AND MS_YSKS.Mzks = GHYY_HYXX.GHKS                        AND MS_YSKS.YSDM = GY_YGDM.YGDM                        AND MS_GHKS.KSDM NOT IN ('3005','3021','3022','3023', '3004')                        AND MS_YSKS.FYXH = MS_MZGHF.FYXH                        AND GHYY_HYXX.TGBZ = 0                        AND GY_DMZD.DMLB = 27                        AND GY_DMZD.DMSB <> 0                        AND GHYY_HYXX.GZRQ > SYSDATE - 1                        AND GHYY_HYXX.GZRQ <= (to_date(SYSDATE) + 7 + 3 / 24)                        AND GY_DMZD.DMSB = GY_YGDM.YGJB  order by YSDM, GZRQ\0 full_hash_value(16) = 765A5805C2D35601EE832F8647919066 hash_value(10) = 1200722022 sql_id(32) = fx0tght3t3436 sql_id(32) = fx0tght3t3436 sql_id(32) = fx0tght3t3436 --//可以发现计算时并没有删除^M chr(13)字符。 --//附上sql_idx.sh脚本。 $ cat -v sql_idx.sh #! /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 odebug=${ODEBUG:-0} oflag=${2:-0} if [ $oflag -eq 0 ] then     sql_text=${1}'\0' fi if [ $oflag -eq 1 ] then #   sql_text="$( cat $1 | dos2unix | sed '$s/;\s*//')"'\0' #   sql_text="$( cat $1 | sed "s/^M$//" | sed '$s/;\s*//')"'\0'    sql_text="$( cat $1 | sed '$s/;\s*//')"'\0' 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 " 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)" 3.收尾： --//修改sql_id.sql 以及sql_idx.sh脚本略。