1.本地连接 sql conn scott/tiger connected. sql select sid,server from v$sessionwhere username=scott; sid server ---------- --------- 42 dedicated sql select sid,server,paddr,processfrom v$session where username=scott; sid server paddr proc
1.本地连接
sql> conn scott/tiger
connected.
sql> select sid,server from v$sessionwhere username='scott';
sid server
---------- ---------
42 dedicated
sql> select sid,server,paddr,processfrom v$session where username='scott';
sid server paddr process
---------- --------- ---------------- ------------------------
42 dedicated 000000009188d050 16592
--此处的16592标识进程
sql> conn scott/tiger
connected.
sql> ho ps -ef|grep 16592
oracle 16592 16531 0 21:58 pts/1 00:00:00 sqlplus
oracle 16733 16592 0 22:02 ? 00:00:00 oracleorcl(description=(local=yes)(address=(protocol=beq)))
–local=yes标识是本地连接，不经过tcp/ip
oracle 16915 16836 0 22:06 pts/2 00:00:00 /bin/bash -c ps -ef|grep 16592
2.远程连接
sql> select sid,server,paddr,processfrom v$session where username='scott';
sid server paddr process
---------- --------- ---------------- ------------------------
42 dedicated 000000009188d050 17041
sql> ho ps -ef|grep 17041
oracle 17041 16531 0 22:11 pts/1 00:00:00 sqlplus
oracle 17131 16836 0 22:14 pts/2 00:00:00 /bin/bash -c ps -ef|grep 17041
远程连接用同本地连接相同的查询方法查不到结果，可以用如下查询：
sql> select spid from v$process whereaddr='000000009188d050';
spid
------------------------
17045
sql> ho ps -ef|grep 17045
oracle 17045 1 0 22:11 ? 00:00:00 oracleorcl (local=no)
–local=no 标识远程连接，走tcp/ip
oracle 17180 16836 0 22:15 pts/2 00:00:00 /bin/bash -c ps -ef|grep 17045
3.连接模式
a．专有 每个用户进程与一个服务进程进行连接
适合消耗pga为主业务比如dss、olap、 dba常规管理任务、dg实现同步--dg需要专有连接
sql> select sid,serial#,server fromv$session where username='scott';
sid serial# server
---------- ---------- ---------
37 236 dedicated
sql> show parameter dispatchers
name type value
----------------------------------------------- ------------------------------
dispatchers string (protocol=tcp) (service=orclxdb)
max_dispatchers integer
b．共享 多个用户进程与一个服务器的子进程进行连接
[oracle@oracle ~]$ sqlplusscott/tiger@oracle:1521/orclxdb
sql> select sid,serial#,server from v$sessionwhere username='scott';
sid serial# server
---------- ---------- ---------
21 536 dedicated
27 1008 shared ---shared/none 标识共享连接
sql> select sid,serial#,server,paddrfrom v$session where username='scott';
sid serial# server paddr
---------- ---------- --------- ----------------
27 1008 none 0000000091888f50
44 1430 none 0000000091888f50
-------注意共享连接不管是多少个，此处的paddr都是一样的。
sql> select sid,serial#,server,paddrfrom v$session where username=’scott’;
sid serial# server paddr
---------- ---------- --------- ----------------
21 536 dedicated 0000000091892190
44 1433 dedicated 0000000091894210
------注意专有连接不管是多少个，每个paddr都是不一样的。
一个服务器进程可以服务多个用户进程，反之则不成立。
4.连接----物理方式通过协议进行连接
会话----在连接基础上产生的一些信息，可以有一个、可以有多个、可以没有
连接关闭，会话结束
会话结束，连接不一定关闭
会话1：
[oracle@oracle ~]$sqlplus scott/tiger
sql> set autot trace stat
会话2：
sql> select sid,serial#,server,paddrfrom v$session where username='scott';
sid serial# server paddr
---------- ---------- --------- ----------------
21 536 dedicated 0000000091892190
34 799 dedicated 0000000091895250
51 252 dedicated 0000000091895250
–--表示在一个连接上产生两个会话
sql> selectsid,serial#,server,paddr,process from v$session where username='scott';
sid serial# server paddr process
---------- ---------- --------- ---------------- ------------------------
21 536 dedicated 0000000091892190 19652
34 799 dedicated 0000000091895250 21284
51 252 dedicated 0000000091895250 21284
--–注意process是相同，即为同一个进程
sql> select spid from v$process whereaddr=' 0000000091895250';
spid
------------------------
21287
想要kill这个会话，可以如下操作：
sql> alter system kill session '51,252';
alter system kill session '51,252'
*
error at line 1:
ora-00031: session marked for kill
可能会出现如上的错误，这虽然能终止该会话，但是不能释放相关资源。
sql> alter system kill session '51,252'immediate;
system altered.
sql> selectsid,serial#,server,paddr,process from v$session where username='scott';
sid serial# server paddr process
---------- ---------- --------- ---------------- ------------------------
21 536 dedicated 0000000091892190 19652
kill一个进程只需要执行如下操作：
sql> selectsid,serial#,server,paddr,process from v$session where username='scott';
sid serial# server paddr process
---------- ---------- ------------------------- ------------------------
21 536 dedicated 0000000091892190 19652
27 1094 dedicated 000000009188d050 21629
sql> select spid from v$process whereaddr='000000009188d050';
spid
------------------------
21632
sql> ho kill -9 21632
sql> select sid,serial#,server,paddr,process from v$session whereusername='scott';
sid serial# server paddr process
---------- ---------- ------------------------- ------------------------
21 536 dedicated 0000000091892190 19652
27 1094 dedicated 000000009188d050 21629
虽然进程被kill了，但是没有退出去，还是能查到，退出之后就查不到了。
http://docs.oracle.com/cd/e11882_01/server.112/e40540/process.htm#cncpt9840