Connecting to an Instance

Before a user can submit a SQL statement to an Oracle database, he must connect to an instance. The user starts a front-end tool, such as SQL*Plus, an alternative such as SQL Navigator or Toad, or runs an application developed to assist with database interaction.

Typically, the user connects and logs on to the Oracle Server. When this happens, a server process is created on the server running the Oracle Server.

This process is responsible for communicating with the Oracle instance on behalf of the user process running on the workstation or the client and for execution of the SQL statements on behalf of the user.

Connection

The connection is a communication pathway between the user process and the Oracle Server. This connection can take three distinct forms:

• A user can log on to the base operating system on which is running the Oracle instance. When logged in, the user then starts a tool (again, SQL*Plus or an application designed for the purpose). The communication channel is created using interprocess communication systems resident on the operating system (TCP/IP, for example).

• A user can launch a tool on her local computer workstation and connect over a network to the server on which the Oracle instance is running. This is the typical client/server configuration, and network software is the communication vehicle on which the transactions travel.

• There can be a three-tiered connection, where the user's computer communicates via a network connection with an application resident on an application server or other network server. This middle tier server then communicates via another network connection to the database server running the Oracle instance. This is known as a dedicated server connection.

Alternatively, users can connect in a shared server configuration where multiple user processes connect to a single shared server process.

A database server configured to allow multiple user processes to share few server processes is known as a shared server configuration. In this configuration, a user process connects to the dispatcher process (Dnnn) whose job is to direct the requests to a queue from which an idle shared server process picks its requests. The request and response queues, as well as other parts found in the PGA in a dedicated server configuration, are found in the SGA when the server is a shared server.

In a shared server environment, the User Global Area (UGA) cannot be stored in the Process Global Area (PGA) because much of the processing is in a dedicated server environment; the UGA is found in the SGA.

Session

A session is considered to be each specific connection of a user to an Oracle Server. It starts as soon as the user is authenticated to the instance and ends either when the user logs off or when there is an abnormal termination of the interface through which the user is connected. Depending on the configuration of the interface through which the user is connecting (for example Oracle E-Business Suite) there can be many concurrent connections possible for the same user ID.

Only the database administrator to perform certain maintenance jobs and either the database administrator (the sysdba role) or the sysoper role to start and/or stop the instance can log in to the instance when it is not up and open for general use.