[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指令碼略。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2850514/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20200211]使用DBMS_SHARED_POOL.MARKHOT與sql_id的計算.txtSQL
- [20180918]檔案格式與sql_id.txtSQL
- [20191012]使用bash從sql_id計算hash_value.txtSQL
- [20220111]該語句的sql_id如何計算的.txtSQL
- txt是什麼格式的檔案 txt格式怎麼弄出來
- chm檔案怎麼轉換成TXT格式？chm檔案快速轉化成TXT格式的方法
- [20200318]crontab檔案格式中的%.txt
- nodejs 統計算指定字尾，格式的檔案NodeJS
- [20220104]in list 幾種寫法效能測試.txt
- [20190329]grep與管道檔案.txt
- [20211230]完善sql_id指令碼.txtSQL指令碼
- 讀取txt檔案將文字行組合成特定格式
- [20241013]sqlplus spool與檔案覆蓋.txtSQL
- elf檔案格式
- FastQ檔案格式AST
- smali 檔案格式
- [20181102]資料檔案改名與awr.txt
- 檔案流下載檔案,zip/其他格式檔案
- 活動目錄域：新增計算機與檔案共享計算機
- php如何上傳txt檔案，並且讀取txt檔案PHP
- Python讀寫txt檔案、轉換csv檔案與pandas條件計數、求和的綜合運用Python
- JPEG格式研究——（2）JPEG檔案格式
- [20181203]改變檔案大小與檢查點.txt
- base64檔案大小計算
- BVH檔案格式解析
- SQLSERVER匯出TXT文字檔案，ORACLE SQL LOADER匯入TXT文字檔案SQLServerOracle
- vsd格式檔案怎麼開啟 vsd是什麼格式的檔案,
- python處理txt檔案Python
- ELF檔案格式與got表hook簡單實現GoHook
- 計算檔案Checksum的幾種方法
- Matlab生成.exe格式檔案Matlab
- Class 檔案格式詳解
- fepk檔案格式說明
- 檔案開啟的格式
- 開啟.ziw格式檔案
- java class 檔案格式解析Java
- hive檔案儲存格式Hive
- win10怎麼把txt格式改成bat win10系統txt檔案改成bat操作方法Win10BAT