本文详细介绍了数据库中的Session和Connection的概念及其相互关系。一个Session可以对应一个或多个Connection，而一个Connection也可以服务于一个或多个Session，这种关系在Oracle数据库中表现为多对多。通过SQL查询示例展示了从一对一到多对一的Session和Connection的对应情况，揭示了两者在网络通信和数据库操作中的动态变化。
经常看到网上关于sessions和connection之间的关系，这里总结一下，方便以后查阅。
一个session可以有一个connection，可以有两个connection，或多个,这种情况只有在share server 模式中才会出现
同样一个connection同样可以有一个session或多少.
所以session和connection之间的关系是多对多之间的关系(当然包一对一)
那到底 session和connection是指什么。
这里引用tom大师的话:
一个connection是一条物理路径从客户端到数据库实例，它是建立在网络或IPC的基础上。
一个session是指一个逻辑单元存在于数据库实例，它可以标识SQL执行，内存分配数据集合等信息。
先看看一对一的情况吧：
C:\Users\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 8 10:25:51 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> select paddr from v$session where username=user;
PADDR
----------------
000007FF5E315D90
SQL> select count(*) from v$process where addr=hextoraw('000007FF5E315D90');
COUNT(*)
----------
1
两个session对应一个connection的情况：
SQL> set autotrace on;
SQL> select paddr,username from v$session where username=user;
PADDR USERNAME
---------------- ------------------------------
000007FF5E315D90 SYS
000007FF5E315D90 SYS
看到查询结果有两条记录，对应同一个paddr。0个session对应一个connection
这里开启两个窗口
窗口1：
C:\Users\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 8 10:25:51 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> select paddr from v$session where username=user;
PADDR
----------------
000007FF5E315D90
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
窗口2：
C:\Users\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 8 10:25:51 2014
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn / as sysdba
Connected.
SQL> select count(*) from v$process where addr=hextoraw('000007FF5E315D90');
COUNT(*)
----------
1
虽然窗口1disconnect，但它的connection并没有关，在窗口2中依然可以查到connection.
扫一扫
2156
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
