SQL*Net is the software provided by Oracle to establish connections between local Oracle database clients and the related Oracle database instance, or in case of database links between 2 different Oracle database instances.

The Transparent Network Substrate (TNS) is the protocol SQL*Net is based on. It is a highly generic logical protocol and therefore supports all common physical network protocols like TCP/IP.

Creating a new user session within a database instance using SQL*Net requires a high level of effort. In contrast, the communication used for the ongoing exchange of data is much simpler.

TNS Packet Header Structure

The communication protocol is based on individual logical packets beeing mapped transparent to physical packets.

Right now 11 different types of packet are in use. Each package has a 8-byte wide global header; the individual packet type is determined through the type field. The packet bodies differ in relation to the underlying type. The 8 identical bytes of the global header are composed of:

  • Total packet size (Size: 2 Bytes)
  • Checksum of packet (Size: 2 Bytes)
  • Type of packet (Size: 1 Byte)
  • Reserved (Size: 1 Byte)
  • Checksum global header (Size: 2 Bytes)

The checksums are calculated in both cases out of the 16 bit ones complement of the 16 bit ones complement of the 16 bit sum.

TNS Packet Types

The 11 different packet types can be roughly divided into 3 groups:

  • Connection
    • Connect (Type: 0x01)
    • Accept (Type: 0x02)
    • Refuse (Type: 0x04)
    • Redirect (Type: 0x05)
  • Data Transfer
    • Data (Type: 0x06)
    • Null – Empty Data (Type: 0x07)
  • Control Flow
    • Abort (Type: 0x09)
    • Resend Packet (Type: 0x0B)
    • Marker Packet (Type: 0x0C)
    • Attention (Type: 0x0D)
    • Control Info (Type: 0x0E)

Theoretically, the possible value of the packet type is in the range of 0x01 … 0x12 (inclusive). Therefore 7 possible values for additional packet types remain.

TNS Packet Body Structure

The structure of the body depends on the type of the packet and can be defined according to the group:

  • Connection
    • Exchange of the minimal version number required by the sender.
    • Current version number, on which the connection is based.
    • Size of Transport Data Unit and Session Data Unit.
    • Number of packets which could be sent before an answer from the server must be received.
    • TestByte to determine the byte order(Value: 0x0001).
    • Actual connection data.
    • Information about the new port to which the connection is relocated.
    • Reason why the connection was not accepted.
  • Data Transfer
    • Actual data to be transmitted with a hint if divided into more then one packet.
    • Security-relevant information.
    • Test data for serverside detection of idle connections.
  • Control Flow
    • Transmission of internal protocol information.
    • Transmission of CTRL-C synchronous/asynchronous.
    • Repeated request of a packet.

Detailed descriptions of the individual packet types with their respective byte-by-byte body structure can be found, for example, on metalink in the document with ID 1007807.6 – SQL* Net Package Structure: NS Packet Header.

SQL*Net Connectivity Scenarios

The basic application of TNS as protocol SQL*Net is based on can be condensed to three possible scenarios depending on the Oracle database instance setup and physical location.

User sessions are handled different in Oracle dedicated and shared database server environments.

In most cases, the client and server operate on different hardware. Typically, there is a TCP/IP based connection between them. If the client needs to establish a connection to the Oracle database server, the IP address and port of the server are defined resolving the service name.

This information is among other deposited on clientside in the files TnsNames.ora or SqlNet.ora located in ORACLE_HOME\network\admin. SqlNet contains mainly basic configuration details like encryption, default domain and tracing options. TnsNames.ora contains typical a list of databases to address.

Oracle Shared Server Database Instance

The client sends a connect packet to the named port of the specified address. The listener determines if an existing dispatcher process can be used. If not a new dispatcher process is created. To establish the connection an accept packet will be sent back to the client directly.

Oracle Dedicated Server Database Instance

The client sends a connect packet to the named port of the specified address. The listener determines if a distinct background process must be created for the proposed connection request.

A redirect packet is sent back as answer to the connection request of the client. This packet contains the port address of a newly created background process of the database instance dedicated only to a single client connection. An accept packet is send after the client has established the connection to his dedicated process. Further requests of the client are sent to this address.

The individual pros and cons of dedicated and shared Oracle database server configurations are not discussed here in detail. However, it is important that the further communication between client and server, such as the exchange of data packets, is identical.

Oracle Dedicated Server Bequeath Connection

When the Oracle database client and the Oracle database instance are installed on the same physical hardware, the so called bequeath connection could be used. As no network traffic is involved, the cycle time is much shorter than for TCP/IP based connections. In addition no Oracle database listener or dispatcher is involved. Therefore it is highly recommended to make use of this, whenever it is possible.

The database instance which is defined in the global system variable ORACLE_SID will start a new background process on request and allocates the space needed direct in the System Global Area (SGA). As there is dedicated communication between the client and the newly created background process bequeath connections are only possible for Oracle dedicated database server environments.

The client sends a connect packet using the operating system specific process communication. The background process answers this with an accept packet. Further communications flow is based on the content of the data packets.

Please note, that the content of the bequeath connect packets sent is different from those used for setting up a TCP/IP based connection.

In case of an impossible or failed attempt to connect, independent of the scenario implemented, a refused packet will be send from the Oracle database instance to the client.


This page was updated 24.04.2011

QueryAdvisor trial.
QueryAdvisor in 30 Seconds

QueryAdvisor quote.
QueryAdvisor support forum.
QueryAdvisor Twitter channel.
social icons Facebook Twitter Blogspot RSS