数据库体系结构读书笔记1

1.查看session的SQL

col machine format a10
col username format a10
select sid,serial#,username,machine,server,paddr,status from v$session;

2.是否存在一个进程多个会话呢？

sys@TEST15> l
  1* select sid,serial#,username,machine,server,paddr,status from v$session
sys@TEST15> set autotrace on statistics
sys@TEST15> /

       SID    SERIAL# USERNAME   MACHINE    SERVER    PADDR    STATUS
---------- ---------- ---------- ---------- --------- -------- --------
       138          1            CESTOS5.4  DEDICATED 2A21F6C4 ACTIVE
       140          1            CESTOS5.4  DEDICATED 2A21F110 ACTIVE
       146          7            CESTOS5.4  DEDICATED 2A21E5A8 ACTIVE
       147         14 SYS        CESTOS5.4  DEDICATED 2A21BDBC INACTIVE
       152          1            CESTOS5.4  DEDICATED 2A21DA40 ACTIVE
       153          1            CESTOS5.4  DEDICATED 2A21D48C ACTIVE
       154          1            CESTOS5.4  DEDICATED 2A21CED8 ACTIVE
       155          1            CESTOS5.4  DEDICATED 2A21C924 ACTIVE
       156          1            CESTOS5.4  DEDICATED 2A21C370 ACTIVE
       159          6 SYS        CESTOS5.4  DEDICATED 2A21BDBC ACTIVE
       160          1            CESTOS5.4  DEDICATED 2A21A138 ACTIVE
       161          1            CESTOS5.4  DEDICATED 2A219B84 ACTIVE
       162          1            CESTOS5.4  DEDICATED 2A2195D0 ACTIVE
       163          1            CESTOS5.4  DEDICATED 2A21901C ACTIVE
       164          1            CESTOS5.4  DEDICATED 2A218A68 ACTIVE
       165          1            CESTOS5.4  DEDICATED 2A2184B4 ACTIVE
       166          1            CESTOS5.4  DEDICATED 2A217F00 ACTIVE
       167          1            CESTOS5.4  DEDICATED 2A21794C ACTIVE
       168          1            CESTOS5.4  DEDICATED 2A217398 ACTIVE
       169          1            CESTOS5.4  DEDICATED 2A216DE4 ACTIVE
       170          1            CESTOS5.4  DEDICATED 2A216830 ACTIVE

21 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1261  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         21  rows processed

我们打开了autotrace发现了，2个会话PADDR一样，说明他们是一个进程的。在这里应该是一个select 和一个autotrace产生的会话。

3.会话与连接
会话出现在v$session视图中，而连接出现在v$process视图中，当sqlplus 中执行 disconnect 命令实际上是取消了会话，但是在v$process中连接没有取消，可以用过connect 再创建会话，只有输入exit后，会话才被取消。我们可以通过v$session视图的paddr和v$process的addr来查看会话与连接的关系
我整理了个SQL来查看会话与连接的对应关系

select b.username osuser,a.username orauser,a.sid,a.serial#,a.machine,a.server,b.addr,b.program,a.status from v$session a ,v$process b where a.paddr( )=b.addr；

4.查看后台进程，有paddr的正在使用的后台进程

select paddr, name, description from v$bgprocess order by paddr desc;