欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

[20180918]文件格式与sql_id.txt

程序员文章站 2022-06-19 23:08:26
[20180918]文件格式与sql_id.txt--//记录测试中遇到的一个问题.这是我在探究SQL*Net more data from client遇到的问题.--//就是实际oracle会把windows的脚本文件转化为linux的文本格式.1.环境:SCOTT@test01p> @ver1 ......

[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字符.