| Process | Packet Number | SQL*Plus Client packet to Database | Oracle Database packet to SQL*Plus Client |
|---|---|---|---|
| Establish TCP connection to database | 1 | TCP SYN to port 1521, client port 58917 | |
| 2 | TCP ACK (packet 1) and SYN response | ||
| 3 | TCP ACK (packet 2) | ||
| Establish TNS connection to database | 4 | TNS Connect request | |
| 5 | TCP ACK (packet 4) | ||
| 6 | TNS Resend | ||
| 7 | TCP ACK (packet 6) | ||
| 8 | TNS Connect request | ||
| 9 | TCP ACK (packet 8) | ||
| 10 | TNS Accept | ||
| Establish SQL*Plus session | 11 | TNS data request (0xdeadbeef0097) | |
| 12 | TNS data response (0xdeadbeef0075) | ||
| 13 | TNS data request (0x0106050403020100 then x86_64/Linux 2.4.xx) | ||
| 14 | TNS data response (0x010600 then Linuxi386/Linux2.0.34-8.1.0.i then more hex) | ||
| 15 | TNS data request (lots of hex) | ||
| 16 | TNS data response (0x028000 and more hex) | ||
| 17 | TCP ACK (packet 16) | ||
| Another couple of dozen TNS Requests and Responses (opaque!) | |||
I spent a while trying to avoid the TNS RESEND, or at least figure out why it was happening. After failing I found Slavik Markovich's blog which suggests the first TNS Connect establishes a listner connection, and the RESEND requests the second TNS Connect to establish the Oracle instance connection.
After the full query has been entered, and the RETURN button had been pressed after the trailing semicolon, or slash, the following conversation takes place.
| Packet Number | SQL*Plus Client packet to Database | Oracle Database packet to SQL*Plus Client |
|---|---|---|
| 1 | TNS data request (0x18fe47a400, other hex and then the query string | |
| 2 | TCP ACK (packet 1) | |
| 3 | TNS data response (0x101700, other hex and then query result) | |
| 4 | TCP ACK (packet 3) | |
| 5 | TNS data request (0x18444fa400 and other hex) | |
| 6 | TCP ACK (packet 5) | |
| 7 | TNS data response (0x17010100, other hex and then ORA-01403: no data found.) | |
| 8 | TCP ACK (packet 7) |
The "ORA-01403: no data found" always occurs, which made me wonder what query it was trying to run after every query. After scouring V$SQL, I eventually came across this wireshark question which plausibly suggests the "ORA-01403: no data found" is Oracle saying "you've already had the last row".
| Packet Number | SQL*Plus Client packet to Database | Oracle Database packet to SQL*Plus Client |
|---|---|---|
| 1 | TNS data request (0x184447a400 (the 4447 varies), other hex and then the PL/SQL block string | |
| 2 | TNS data response (0x080600, then other hex) | |
| 3 | TCP ACK (packet 2) |
After issuing the command set serveroutput on size unlimited the following conversation occurs.
| Packet Number | SQL*Plus Client packet to Database | Oracle Database packet to SQL*Plus Client |
|---|---|---|
| 1 | TNS data request (0x180251a400, other hex and then BEGIN DBMS_OUTPUT.ENABLE(NULL); END; | |
| 2 | TNS data response (0x080600, then other hex) | |
| 3 | TCP ACK (packet 2) | |
| 4 | TNS data request (0x184d51a400, other hex and then DBMSOUTPUT_LINESARRAY and more hex | |
| 5 | TNS data response (0x0d020200, then other hex and strings) | |
| 6 | TNS data request (0x184d51a400, and more hex) | |
| 7 | TNS data response (0x0d012400 and more hex) | |
| 8 | TCP ACK (packet 7) |
| Packet Number | SQL*Plus Client packet to Database | Oracle Database packet to SQL*Plus Client |
|---|---|---|
| 1 | TNS data request (0x184d51a400 (the 4d51 varies), other hex and then the PL/SQL block string | |
| 2 | TNS data response (0x080600, then other hex) | |
| 3 | TCP ACK (packet 2) | |
| 4 | TNS data request (0x18e15aa400, other hex then BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUM_LINES); END; and more hex | |
| 5 | TNS data response (0x0b050200 and more hex, including the DBMS output strings) | |
| 6 | TCP ACK (packet 5) |
Essentially the first three packets are the same as the DBMS_OUTPUT turned off case, with the second three packets used whenever DBMS_OUTPUT is turned on to pick up any DBMS_OUTPUT produced.