When debugging database interactions between applications and Oracle servers, Wireshark becomes an essential tool. The specific challenge arises when you need to:

• Capture TNS (Transparent Network Substrate) protocol packets
• Identify SQL statements within these packets
• Filter based on specific string patterns in the SQL

First, let's establish a foundation for capturing Oracle TNS traffic:

tcp.port == 1521 || tns

This filter will show all traffic on the default Oracle port (1521) and any packets identified as TNS protocol.

To filter packets containing specific SQL strings, we use Wireshark's contains operator combined with byte-level examination:

tns and tcp.payload contains "EMPLOYEES"

This will show all TNS packets where the TCP payload contains the string "EMPLOYEES". Note that Oracle TNS packets may span multiple TCP packets, so you might need to:

1. Right-click the packet
2. Select "Follow TCP Stream"
3. Look for your SQL in the complete conversation

Oracle TNS packets often use specific character encodings. If your string filter isn't working, try:

tns and frame contains 45:4d:50:4c:4f:59:45:45:53 (hex representation of "EMPLOYEES")

For case-insensitive searches:

tns and upper(tcp.payload) contains "FROM EMPLOYEES"

Let's say you're investigating performance issues with queries against the SALES table:

tns and (tcp.payload contains "FROM SALES" or tcp.payload contains "JOIN SALES")

You can combine this with display filters to see only relevant packets:

tns and tcp.payload contains "WHERE CUSTOMER_ID" and !(tcp.payload contains "ORDER BY")

• Use Edit → Find Packet with "String" and "Packet bytes" options
• Bookmark interesting packets with Ctrl+B
• Export filtered packets to a new capture file for focused analysis
• Combine with time filters: frame.time >= "2023-01-01 09:00:00"

For automated processing, you can use Wireshark's command-line version:

tshark -r capture.pcap -Y 'tns and tcp.payload contains "UPDATE"' -T fields -e tcp.payload

This command will extract all TNS packets containing SQL UPDATE statements from your capture file.

---

When debugging an application interacting with an Oracle database, packet sniffing becomes essential if application logging is insufficient. To capture TNS (Transparent Network Substrate) traffic in Wireshark:

1. Start Wireshark
2. Select the network interface used by your application
3. Apply a basic filter: tcp.port == 1521 (default Oracle port)
4. Begin capturing packets

To isolate packets containing particular SQL statements in the payload:

1. Use the contains operator in Wireshark's display filter
2. Example: tcp.payload contains "SELECT"
3. For case-insensitive search: tcp.payload matches "(?i)select"
4. Combine with TNS protocol: tns and tcp.payload contains "EMPLOYEES"

For more precise filtering of Oracle TNS packets:

1. Filter by TNS packet type: tns.type == 6 (for TNS requests)
2. Combine with string search: tns.type == 6 && tcp.payload contains "WHERE"
3. Use hex representation for special characters: tcp.payload contains 7c (pipe character)
4. Filter by packet length: tcp.len > 100 && tcp.payload contains "UPDATE"

Let's say you need to find all queries accessing the EMPLOYEES table:

tns and tcp.payload matches "(?i)from\\s+employees"

This regex pattern will match:
- FROM EMPLOYEES
- from employees
- From employees (with multiple spaces)

When you find matching packets:

1. Right-click the packet → Follow → TCP Stream
2. Look for the SQL statement in the ASCII representation
3. Note the source/destination IPs and ports
4. Use Right-click → Apply as Filter to isolate conversation

String filtering can be resource-intensive. For large captures:

1. First apply a basic filter: tns
2. Save the filtered output to a new file
3. Then apply your string filter
4. Alternatively, use tshark command-line version for batch processing

Create a custom column showing SQL fragments:

1. Go to Edit → Preferences → Appearance → Columns
2. Add new column with name "SQL Fragment"
3. Set type: "Custom", Field: "tcp.payload"
4. Use display filter: tcp.payload contains "SELECT"