[20180918]文件格式与sql_id.txt

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

[20180918]文件格式与sql_id.txt --//记录测试中遇到的一个问题.这是我在探究SQL*Net more data from client遇到的问题. --//就是实际oracle会把windows的脚本文件转化为linux的文本格式. 1.环境: SCOTT@test01p> @ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 --//unix与dos的文本格式存在区别,就是每行的结尾windows是0x0d 0x0a.而linux是0x0a. D:\temp\test>cat dept_dos.txt select * from dept; D:\temp\test>cat dept_unix.txt select * from dept; --//这样看上去2个文件一样的实际上.实际上1个dos格式,1个unix格式. D:\temp\test>ls -l dept* -rw-rw-rw- 1 user group 24 Sep 19 22:58 dept_dos.txt -rw-rw-rw- 1 user group 20 Sep 19 23:01 dept_unix.txt --//unix格式的对应文件少4个字节.正好4行. 2.测试: SCOTT@test01p> @ dept_dos.txt DEPTNO DNAME LOC ---------- -------------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS DALLAS SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 3knyh7z401k69, child number 0 ------------------------------------- select * from dept Plan hash value: 3383998547 --------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 --//sql_id=3knyh7z401k69. SCOTT@test01p> @ dept_unix.txt DEPTNO DNAME LOC ---------- -------------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS DALLAS SCOTT@test01p> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 3knyh7z401k69, child number 0 ------------------------------------- select * from dept Plan hash value: 3383998547 --------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 --//sql_id=3knyh7z401k69. --//可以发现sql_id一样的.如果你使用一些网络抓包软件tcpdump,也可以验证这个问题. SCOTT@test01p> select sql_id,hash_value,sql_text,executions from v$sql where sql_id='3knyh7z401k69'; SQL_ID HASH_VALUE SQL_TEXT EXECUTIONS ------------- ---------- ------------------ ---------- 3knyh7z401k69 3355494601 select * from dept 2 --//这样执行2次. SCOTT@test01p> select distinct name,hash_value,full_hash_value from V$DB_OBJECT_CACHE where hash_value='3355494601'; NAME HASH_VALUE FULL_HASH_VALUE -------------------- ---------- -------------------------------- select * from dept 3355494601 de2b693a53b60a083953d03fc800c8c9 SCOTT@test01p> select dump(sql_text,16) c70 from v$sql where sql_id='3knyh7z401k69'; C70 ---------------------------------------------------------------------- Typ=1 Len=18: 73,65,6c,65,63,74,20,2a,20,66,72,6f,6d,20,64,65,70,74 --//注这里看到也是不一致的.里面有空格.可以在toad观察sql_fulltext. --//建立文件aaa.txt ,xxd查看如下: 00000000: 7365 6c65 6374 0a2a 0a66 726f 6d0a 6465 select.*.from.de 00000010: 7074 00 pt. --//在vim下执行:set binary,保存文件aaa.txt D:\temp\test>cat aaa.txt | md5sum |sed "s/ -//" | D:\tools\Vim\vim80\xxd -r -p | od -t x4 | sed -n -e "s/^0000000 //" -e "s/ //gp" de2b693a53b60a083953d03fc800c8c9 --//在linux下执行: # echo -n -e 'select\n*\nfrom\ndept\0' |md5sum |sed "s/ -//" | xxd -r -p | od -t x4 | sed -n -e "s/^0000000 //" -e "s/ //gp" de2b693a53b60a083953d03fc800c8c9 --//full_hash_value=de2b693a53b60a083953d03fc800c8c9一致. --//再次验证oracle把执行文本转化为linux格式.也就是0x0d0x0a变成了0x0a. 3.上班在linux测试环境重复测试看看: 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 $ unix2dos dept_dos.txt unix2dos: converting file dept_dos.txt to DOS format ... $ xxd -c 16 dept_dos.txt 0000000: 7365 6c65 6374 0d0a 2a0d 0a66 726f 6d0d select..*..from. 0000010: 0a64 6570 743b 0d0a .dept;.. --//注意看现在是dos格式. SCOTT@book> @ dept_dos.txt DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SCOTT@book> @ &r/dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 3knyh7z401k69, child number 0 ------------------------------------- select * from dept Plan hash value: 3383998547 --------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 --//sql_id=3knyh7z401k69,与前面一样. --//通过tcpdump抓包看看. # tcpdump -l -i eth0 -s 0 -A port 1521 and host 192.168.98.6 -nn -w t.txt tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes ^C5 packets captured 6 packets received by filter 0 packets dropped by kernel # xxd -c 16 t.txt 0000000: d4c3 b2a1 0200 0400 0000 0000 0000 0000 悦.............. 0000010: ffff 0000 0100 0000 34f7 a25b 63d9 0600 ......4..[c?. 0000020: 4901 0000 4901 0000 a0d3 c1f4 713c 0cda I...I....恿魆<.? 0000030: 41b6 4e07 0800 4500 013b 2aa2 4000 7f06 A.N...E..;*.@... 0000040: 8875 c0a8 6206 c0a8 644e c9b9 05f1 87fc .u?b.?dN?.?? 0000050: 15d1 73b5 7d4c 5018 3f02 0666 0000 0113 .裺祡LP.?..f.... 0000060: 0000 0600 0000 0000 1169 20fe ffff ffff .........i ? 0000070: ffff ff01 0000 0002 0000 0003 5e21 6180 .........^!a. 0000080: 0000 0000 0000 feff ffff ffff ffff 1200 ......?.. 0000090: 0000 feff ffff ffff ffff 0d00 0000 feff ..?....? 00000a0: ffff ffff ffff feff ffff ffff ffff 0000 ?.. 00000b0: 0000 0100 0000 0000 0000 0000 0000 0000 ................ 00000c0: 0000 0000 0000 0000 0000 0000 0000 feff ..............? 00000d0: ffff ffff ffff 0000 0000 0000 0000 feff ........? 00000e0: ffff ffff ffff feff ffff ffff ffff feff ?? 00000f0: ffff ffff ffff 0000 0000 0000 0000 feff ........? 0000100: ffff ffff ffff feff ffff ffff ffff 0000 ?.. 0000110: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 0000120: 0000 0000 0000 0000 0000 1273 656c 6563 ...........selec ~~~~~~~~~~~~~~ 0000130: 740a 2a0a 6672 6f6d 0a64 6570 7401 0000 t.*.from.dept... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 0000140: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 0000150: 0000 0000 0000 0000 0001 0000 0000 0000 ................ 0000160: 0000 8000 0000 0000 0000 0000 0000 0000 ................ --//注意前面的0x12=18表示sql语句长度. $ xxd -c 32 dept_dos.txt 0000000: 7365 6c65 6374 0d0a 2a0d 0a66 726f 6d0d 0a64 6570 743b 0d0a select..*..from..dept;.. --//上下对比就明白在传输到服务端时已经丢弃了0x0d字符.

编辑推荐：

返回列表

[20180918]文件格式与sql_id.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推