ORACLE记录登录登出以及控制连接数（注意：是连接数而不是会话数）

最近做了一下关于登录登出以及控制连接数的功能，今天做一下总结，

功能：

1. 只记录建立连接的那一个会话，建立连接后再产生的会话不做记录。（如：登录PLSQL Developer，再从PLSQL Developer里打开sql窗口执行语句，这样就会产生两个会话，再打开就会继续产生会话，我只记录登录PLSQL Developer那会的会话，在里面打开sql窗口所产生的会话不记录）
2. 控制连接数为5个，大于5个则不给登录

连接和会话的区别请参考一下这位仁兄的形象例子：http://hi.baidu.com/bystander1983/blog/item/7201a3835d1961ab0cf4d294.html

 

登陆登出触发器：

先建立一个登录登出日志表

CREATE TABLE LOGIN_LOG
(
  SESSION_ID     NUMBER(8) NOT NULL, --会话ID
  LOGIN_ON_TIME  DATE,   --登录时间
  LOGIN_OFF_TIME DATE,  --登出时间
  USERNAME     VARCHAR2(100),  --数据库用户名
  MACHINE        VARCHAR2(50),  --机器名
  ACCESS_PROGRAM    VARCHAR2(50),  --接入程序
  IP_ADDRESS     VARCHAR2(20) --登录机器的ip地址
)

创建登录触发器：（这里需要注意的一点是：对于拥有dba权限的用户RAISE_APPLICATION_ERROR参数是没效果的，也就是对于这种用户是不能限制他的连接数）

CREATE OR REPLACE TRIGGER LOGIN_ON_INFO
  AFTER LOGON ON DATABASE
DECLARE
  V_COUNT NUMBER;
BEGIN
  --查出连接总数，经测试：如果同一连接里面的会话V$SESSION的PROCESS是相同的。
  SELECT COUNT(DISTINCT PROCESS)
    INTO V_COUNT
    FROM V$SESSION
   WHERE USERNAME =
         (SELECT USERNAME FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID'));
  --判断会话总数
  IF V_COUNT >= 2 THEN
    RAISE_APPLICATION_ERROR(-20001, '用户连接数大于5，不能登录！');
  END IF;
  --判断此会话所在连接里的总会话数
  SELECT COUNT(*)
    INTO V_COUNT
    FROM V$SESSION
   WHERE PROCESS IN
         (SELECT PROCESS FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID'));
  --若此会话所在连接里的总会话数等于1，则说明此连接是新建的，记录其登录帐号和IP地址等信息
  IF V_COUNT = 1 THEN
    INSERT INTO LOGIN_LOG
      SELECT AUDSID,
             SYSDATE,
             NULL,
             SYS.LOGIN_USER,
             MACHINE,
             PROGRAM,
             SYS_CONTEXT('USERENV', 'IP_ADDRESS')
        FROM V$SESSION
       WHERE AUDSID = USERENV('SESSIONID');
  END IF;
END;

创建登出触发器：

CREATE OR REPLACE TRIGGER LOGIN_OFF_INFO
BEFORE LOGOFF
ON DATABASE
BEGIN
  UPDATE LOGIN_LOG
     SET LOGIN_OFF_TIME = SYSDATE
   WHERE SESSION_ID = USERENV('SESSIONID');
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;

这个登出触发器有个缺点：它不管LOGIN_LOG这个表里面有没有记录这个会话都会执行update操作，还有异常退出的时候也是不会记录（如结束进程）

 

---------------------------------------------------------------------------      测试      ------------------------------------------------------------------------

测试登录登出如下：
1.用diyyong用户登陆数据库

2.查询 login_log 表
 

上面成功记录到日志。

3.在这个连接里面新建会话：

4.查看是否会记录同一连接的其他会话

成功：同一个连接首个会话会记录，其他会话没记录。

5.登出记录

测试：成功。

测试限制连接数：

图片后方diyyong帐户是已登录，弹出框为diyyong是第二次登录，弹错，测试通过！

 

有错或有建议的欢迎指正！