[20240320]空格与sqlpus的sql语句.txt

来源：这里教程网时间：2026-03-03 19:43:01 作者：

[20240320]空格与sqlpus的sql语句.txt --//优化sql语句时遇到的问题，自己上我发现我手工执行获得的sql_id与程序里面的sql_id不一致,原因很多sqlplus下如果是dos文本格 --//式,计算的文本会将\r\n字符变成\n,如果多行,文本结尾的空格会删除等等,通过例子说明: --//本文的deptno = 10 应该是连在一起的. 1.环境： SCOTT@book> @ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试1： --//开头存在空格. SCOTT@book> select * from dept where deptno = 10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 832478979 agw1v10stx7s3 0 40707 2852011669 319e9f03 2024-03-20 08:31:08 16777216 SCOTT@book> select * from dept where deptno = 10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 911274289 4xamnunv51w9j 0 61745 2852011669 3650f131 2024-03-20 08:31:14 16777216 SCOTT@book> select sql_text from v$sql where sql_id in ('agw1v10stx7s3','4xamnunv51w9j'); SQL_TEXT ------------------------------------------------------------ select * from dept where deptno = 10 select * from dept where deptno = 10 --//并没有删除开头的sql语句。 3.测试2: --//执行脚本的结尾存在空格. SCOTT@book> select * from dept where deptno = 10 ; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 2103677907 8qprua5yq74ym 0 103379 2852011669 7d6393d3 2024-03-22 15:58:00 16777216 SCOTT@book> select * from dept where deptno = 10; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 911274289 4xamnunv51w9j 0 61745 2852011669 3650f131 2024-03-22 15:58:05 16777216 SCOTT@book> select sql_text||'A' c60 from v$sql where sql_id in ('8qprua5yq74ym','4xamnunv51w9j'); C60 ------------------------------------------------------------ select * from dept where deptno = 10A select * from dept where deptno = 10 A --//很明显分号结尾的空格计算在内. 4.测试3: --//建立文件b1.txt,unix文件格式。 $ cat -Ev b1.txt select * from dept where $ deptno=20;$ --//where后面有4个空格。 SCOTT@book> @ b1.txt DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 561182986 4rj7xbhhr5y8a 0 63754 2852011669 2172f90a 2024-03-20 09:04:38 16777221 --//记下sql_id=4rj7xbhhr5y8a --//首先看看v$sql视图sql_text与sql_fulltext的区别： SCOTT@book> select sql_text from v$sql where sql_id='4rj7xbhhr5y8a'; SQL_TEXT ------------------------------------------------------------ select * from dept where deptno=20 SCOTT@book> select sql_fulltext c60 from v$sql where sql_id='4rj7xbhhr5y8a'; C60 ------------------------------------------------------------ select * from dept where deptno=20 --//可以看出v$sql视图sql_text与sql_fulltext的区别，sql_text删除结尾的空格，并且过滤了\n字符，而sql_fulltext基本原样输出。 --//另外注意一点两者sql_text，sql_fulltext数据类型不一样，前者varchar2(1000)，后者clob类型。 --//sql_id的计算问题： SCOTT@book> spool b31.txt SCOTT@book> @ sql_idz 4rj7xbhhr5y8a --SQL_ID = 4rj7xbhhr5y8a select * from dept where deptno=20; SCOTT@book> spool off $ cat -Ev b31.txt SCOTT@book> @ sql_idz 4rj7xbhhr5y8a$ $ --SQL_ID = 4rj7xbhhr5y8a$ $ select * from dept where$ deptno=20;$ $ SCOTT@book> spool off$ --//注意看where后面的空格丢失了,也许输出忘记考虑trimspool的设置问题。看看计算如何. $ ~/bin/sql_idx.sh b1.txt 1 > bb.txt sql_text = select * from dept where $ deptno=20\0$ full_hash_value(16) = C141E01E5D8842BB39E5FAFE98FC8176 $ hash_value(10) = 2566685046 $ sql_id(32) = 3mtguzucgt0bq$ sql_id(32) = 3mtguzucgt0bq$ sql_id(32) = 3mtguzucgt0bq$ --//很明显使用我写的脚本计算sql_id=3mtguzucgt0bq与sqlplus程序执行的sql_id=4rj7xbhhr5y8a不同. --//改写如下： $ cp b1.txt b2.txt $ vi b2.txt $ cat -Ev b2.txt select * from dept where$ deptno=20;$ $ ~/bin/sql_idx.sh b2.txt 1 >| bb1.txt $ cat -Ev bb1.txt sql_text = select * from dept where$ deptno=20\0$ full_hash_value(16) = E375F34FDCD15D104BC4FD5C2172F90A $ hash_value(10) = 561182986 $ sql_id(32) = 4rj7xbhhr5y8a$ sql_id(32) = 4rj7xbhhr5y8a$ sql_id(32) = 4rj7xbhhr5y8a$ --//很明显sql_id计算使用的是b2.txt的文件计算的，where后面的空格取消了。 5.测试4： --//再来看看文件格式问题。 $ unix2dos b1.txt unix2dos: converting file b1.txt to DOS format ... SCOTT@book> @ b1.txt DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 561182986 4rj7xbhhr5y8a 0 63754 2852011669 2172f90a 2024-03-20 09:24:04 16777224 --//还是一样，说明还是按照b2.txt的文件计算sql_id。 6.测试5: --//建立文件bx.sql,unix文件格式。 $ cat -Ev bx.sql select ^M * ^M from ^M dept ^Mwhere ^Mdeptno=20;$ SCOTT@book> @ bx.sql DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 1668358914 cymmqupjr28s2 0 74498 2852011669 63712302 2024-03-20 15:16:34 16777221 SCOTT@book> select sql_text from v$sql where sql_id='cymmqupjr28s2'; SQL_TEXT ------------------------------------------------------------ deptno=20 SCOTT@book> select sql_fulltext from v$sql where sql_id='cymmqupjr28s2'; SQL_FULLTEXT ----------------------------------------------------------- deptno=20 --//^M的输出导致输出看的非常怪异!! SCOTT@book> @ sql_idx cymmqupjr28s2 --SQL_ID = cymmqupjr28s2 deptno=20; SCOTT@book> @ sql_idz cymmqupjr28s2 --SQL_ID = cymmqupjr28s2 deptno=20; SCOTT@book> @ sql_id cymmqupjr28s2 --SQL_ID = cymmqupjr28s2 select * from dept where deptno=20; --//注:我写的3个查看sql_id的版本,sql_id.sql版本过滤掉^M.sql_idz.sql的保持原样输出. SCOTT@book> spool by.txt SCOTT@book> select sql_fulltext from v$sql where sql_id='cymmqupjr28s2'; SQL_FULLTEXT ------------------------------------------------------------------------- deptno=20 SCOTT@book> spool off --//编辑by.txt后如下： $ cat -Ev by.txt select ^M * ^M from ^M dept ^Mwhere ^Mdeptno=20$ $ ~/bin/sql_idx.sh by.txt 1 | cat -Ev sql_text = select ^M * ^M from ^M dept ^Mwhere ^Mdeptno=20\0$ full_hash_value(16) = 0E6C6BF775D7AA8DCF4E76D563712302 $ hash_value(10) = 1668358914 $ sql_id(32) = cymmqupjr28s2$ sql_id(32) = cymmqupjr28s2$ sql_id(32) = cymmqupjr28s2$ $ ~/bin/sql_idx.sh bx.sql 1 | cat -Ev sql_text = select ^M * ^M from ^M dept ^Mwhere ^Mdeptno=20\0$ full_hash_value(16) = 0E6C6BF775D7AA8DCF4E76D563712302 $ hash_value(10) = 1668358914 $ sql_id(32) = cymmqupjr28s2$ sql_id(32) = cymmqupjr28s2$ sql_id(32) = cymmqupjr28s2$ --//可以看出中间出现的^M不过过滤. 7.继续测试： --//重新编辑bx.sql，内容如下： $ cat -Ev bx.sql select ^M$ * $ ^M $ from ^M $ dept ^Mwhere ^Mdeptno=20;$ SCOTT@book> SET SQLBLANKLINES OFF SCOTT@book> @ bx.sql SP2-0042: unknown command "from" - rest of line ignored. wher..." - rest of line ignored.ing "dept --//存在一个空行。 SCOTT@book> SET SQLBLANKLINES ON SCOTT@book> @ bx.sql DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 987480685 a9mmzpcxdrhmd 0 115309 2852011669 3adbc26d 2024-03-20 15:27:59 16777220 SCOTT@book> select sql_text from v$sql where sql_id='a9mmzpcxdrhmd'; SQL_TEXT ------------------------------------------------------------ deptno=20* from dept SCOTT@book> select sql_fulltext c60 from v$sql where sql_id='a9mmzpcxdrhmd'; C60 ------------------------------------------------------------ select * from deptno=20 SCOTT@book> @ sql_id a9mmzpcxdrhmd --SQL_ID = a9mmzpcxdrhmd select * from dept where deptno=20; SCOTT@book> @ sql_idx a9mmzpcxdrhmd --SQL_ID = a9mmzpcxdrhmd select * from deptno=20; SCOTT@book> @ sql_idz a9mmzpcxdrhmd --SQL_ID = a9mmzpcxdrhmd select * from deptno=20; SCOTT@book> spool by.txt SCOTT@book> select sql_fulltext c60 from v$sql where sql_id='a9mmzpcxdrhmd'; C60 ------------------------------------------------------------ select * from deptno=20 SCOTT@book> spool off $ cat -Ev by.txt SCOTT@book> select sql_fulltext c60 from v$sql where sql_id='a9mmzpcxdrhmd';$ C60$ ------------------------------------------------------------$ select$ *$ $ from$ dept ^Mwhere ^Mdeptno=20$ $ $ SCOTT@book> spool off$ --//为了对比方便,复制上面的cat -Ev bx.sql输出: $ cat -Ev bx.sql select ^M$ * $ ^M $ from ^M $ dept ^Mwhere ^Mdeptno=20;$ --//对比bx.sql的内容: --//第1行select后面的空格,^M消失。 --//第2行*后面的空格消失。 --//第3行全部删除。 --//第4行from后面的空格以及^M删除. --//第5行保留中间出现的的^M。 --//编辑by.txt $ cat -Ev by.txt select$ *$ $ from$ dept ^Mwhere ^Mdeptno=20$ $ ~/bin/sql_idx.sh by.txt 1 | cat -Ev sql_text = select$ *$ $ from$ dept ^Mwhere ^Mdeptno=20\0$ full_hash_value(16) = D830D3E1D201C57AA4CE7FAB3ADBC26D $ hash_value(10) = 987480685 $ sql_id(32) = a9mmzpcxdrhmd$ sql_id(32) = a9mmzpcxdrhmd$ sql_id(32) = a9mmzpcxdrhmd$ --//可以确定sql_id计算使用的是v$sql.sql_fulltext计算的。 SCOTT@book> spool bz.txt SCOTT@book> @ sql_idz a9mmzpcxdrhmd --SQL_ID = a9mmzpcxdrhmd select * from deptno=20; SCOTT@book> spool off $ cat -Ev bz.txt select$ *$ $ from$ dept ^Mwhere ^Mdeptno=20;$ $ ~/bin/sql_idx.sh bz.txt 1 | cat -Ev sql_text = select$ *$ $ from$ dept ^Mwhere ^Mdeptno=20\0$ full_hash_value(16) = D830D3E1D201C57AA4CE7FAB3ADBC26D $ hash_value(10) = 987480685 $ sql_id(32) = a9mmzpcxdrhmd$ sql_id(32) = a9mmzpcxdrhmd$ sql_id(32) = a9mmzpcxdrhmd$ 8.总结: --//我以前一直认为都是按照sqlplus的方式计算的，实际上并不是这样的情况，其它程序比如PB开发的程序或者w3wp.exe的程序，里面 --//sql语句是保持"原样",大部分应该如此. --//这样就好解析我生产系统遇到的问题。 --//我生成的脚本过滤掉chr(13)变成unix格式，在这种情况下计算的sql_id肯定不一样，另外结尾的空格也保留下来，总之sqlplus下执 --//行获得sql_id 与其它程序的sql_id不同是很正常的情况。 --//你可以测试最新的toad下结尾的空格是保留的,早期的toad版本是在执行前在最后一行加入1个空格,最新的版本没有这个问题. --//参考: --//[20120327]toad与sqlplus下执行sql语句的一个细节.txt ->https://blog.itpub.net/267265/viewspace-719592/ --//[20150803]toad 12版本1个小变化.txt -> https://blog.itpub.net/267265/viewspace-1760071/ 9.附上相关脚本: $ cat sql_idz.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 SQL_FULLTEXT||';' SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM = 1; --SELECT REPLACE (SQL_FULLTEXT||';', '', '') SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM = 1; --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 SQL_TEXT||';' INTO V_SQL_FULLTEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1; --SELECT REPLACE (SQL_TEXT||';','','') INTO V_SQL_FULLTEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1; --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 $ cat ~/bin/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' $//" | sed '$s/;\s*//')"'\0'd "s/ 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)"

编辑推荐：

返回列表

[20240320]空格与sqlpus的sql语句.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推