Connections and Sessions

Connections and Sessions

Aconnection is a physical communication pathway between a client process and a database instance. A communication pathway is established using available interprocess communication mechanisms or network software. Typically, a connection occurs between a client process and a server process or dispatcher, but it can also occur between a client process and Oracle Connection Manager (CMAN).

Asession is a logical entity in the database instance memory that represents the state of a current user login to a database. For example, when a user isauthenticated by the database with a password, a session is established for this user. A session lasts from the time the user is authenticated by the database until the time the user disconnects or exits the database application.

A single connection can have 0, 1, or more sessions established on it. The sessions are independent: a commit in one session does not affecttransactions in other sessions.

Note:

If Oracle Net connection pooling is configured, then it is possible for a connection to drop but leave the sessions intact.

Multiple sessions can exist concurrently for a single database user. As shown inFigure 15-2, userhr can have multiple connections to a database. In dedicated server connections, the database creates a server process on behalf of each connection. Only the client process that causes the dedicated server to be created uses it. In a shared server connection, many client processes access a single shared server process.

Figure 15-2 One Session for Each Connection

Description of "Figure 15-2 One Session for Each Connection"

Figure 15-3 illustrates a case in which userhr has a single connection to a database, but this connection has two sessions.

Figure 15-3 Two Sessions in One Connection

Description of "Figure 15-3 Two Sessions in One Connection"

Generating an autotrace report of SQL statement execution statistics re-creates the scenario inFigure 15-3.Example 15-2 connects SQL*Plus to the database as user SYSTEM and enables tracing, thus creating a new session (sample output included).

Example 15-1 One Connection with Two Sessions

SQL> SELECT SID, SERIAL#, PADDR FROM V$SESSION WHERE USERNAME = USER;

SID SERIAL# PADDR
--- ------- --------
 90      91 3BE2E41C
 
SQL> SET AUTOTRACE ON STATISTICS;
SQL> SELECT SID, SERIAL#, PADDR FROM V$SESSION WHERE USERNAME = USER;
 
SID SERIAL# PADDR
--- ------- --------
 88      93 3BE2E41C
 90      91 3BE2E41C
...
SQL> DISCONNECT

The DISCONNECT command in Example 15-1 actually ends the sessions, not the connection. Opening a new terminal and connecting to the instance as a different user, the query inExample 15-2 shows that the connection from Example 15-1 is still active.

Example 15-2 Connection with No Sessions

SQL> CONNECT dba1@inst1
Password: ********
Connected.
SQL> SELECT PROGRAM FROM V$PROCESS WHERE ADDR = HEXTORAW('3BE2E41C');

PROGRAM
------------------------------------------------
oracle@stbcs09-1 (TNS V1-V3)

See Also:

"Shared Server Architecture"

注释：

TheUGA must be available to a database session.

这句话说明，

第一，           会话是一个逻辑概念（不是指oracle里说的表空间是逻辑对象，数据文件是物理对象的那种逻辑的意思，是一般意义上的逻辑的意思），是一个逻辑上存在的概念。不像说马这个概念，就有一个对应的实物，或说表的一个区段，就有一个物理的存储在磁盘上的区段。会话是什么，when a user is authenticated by the database with a password, asession is established for this user，即一个用户（账号）通过了服务端的oracle软件系统的验证后，该用户就可以实现它的目的，就是操作oracle数据库上的数据，而用户通过验证后在那里操作数据库数据的这一个过程就是会话。顺便说下，一个处于客户端的用户（账号）要想通过服务端的oracle软件系统的验证，还想要通过验证后在那里操作数据库数据。要实现这些行为，首先，起码的条件是客户端和服务端之间要具有能通讯的能力，这就要求两者间建立一条连接。就像两个人要谈生意（谈生意就是一个会话），两个人又不能见面谈，那他们就要通过手机打通对方的电话，这样就能听到对方的声音了（就是建立一条连接），之后，才能谈生意（谈生意就是一个会话）。这就是Connections and Sessions之间的区别（和联系）。

第二，为了记录会话即一个过程的相关信息，就会有一块内存空间开辟出来用于保存这些信息，这块内存空间就是UGA。

顺便说下，专用服务器进程和共享服务器进程的区别在于前者只能最多有一个客户端进程和它建立一条连接，而后者则是可以和多个客户端进程同时建立连接。而不是说区别在于前者之后能同时有一个会话（内存），后者可以多个。其实前者也可以同时有多个会话（内存）。所以是否同时有多个会话（内存）不是两种进程间的区别。

参考：

会话的属性以及组建级别和客户端级别的SQL语句跟踪