利用oracle session的client_info記錄用戶登入信息

 
當有不同的用戶或由不同的系統登入到Oracle系統時,
為便於全局跟蹤該用戶由哪台機器登入的,登入的是哪個系統,登入時間,登入帳戶等.
一般會在登入或切換窗口時用table記錄相關信息.如我們的物控系統中的y3_login
 
y3_login記錄方式有以下問題.
1. insert後需要commit, 可能影響到別的事務. (或可用自治事務)
2. table一般不是全局的, 多個系統無法共用.
3. 網絡中段異常退出時會無法及時清理帳戶信息.
4. 同一ip,同一帳戶多次登入,不便於區分
 
個人建議解決方案如下:  
在Oracle 登入數據字典中有一個欄位v$session.client_info VARCHAR2(64).
不超過64字節的信息可以用 dbms_application_info.set_client_info 來修改保存. 退出或中斷系統會自動清除.
用package  pkg_clientinfo重新封裝,以變於修改信息.

create or replace package pkg_clientinfo is

 procedure set_info(id varchar2, val varchar2) ;
 function get_info(id varchar2) return varchar2 ;

end pkg_clientinfo;


/
create or replace package body pkg_clientinfo is

/********
用分號來隔離不同段信息
******/
procedure set_info(id varchar2, val varchar2) is
v_info varchar2(64) ;
n_info varchar2(64) ;
BEGIN
   v_info := sys_context('USERENV', 'CLIENT_INFO') ;

   for r in (select column_value data from table(func_split(v_info))) loop
      if length(r.data)>1 and substr(r.data, 1, length(id)+1) <> id ||':' then
         n_info := n_info||r.data||';';
      end if ;
   end loop;
   if val is not null then
     n_info := n_info||id||':'||val ;
   end if;
   dbms_application_info.set_client_info(n_info);
END;

function get_info(id varchar2) return varchar2 is
  tmp varchar2(64) ;
begin
  dbms_application_info.read_client_info(tmp) ;
  return substr(regexp_substr(tmp, id||':[^;]+'), length(id)+2);
end ;



/*
 select count(*)
 from v$session where type='USER'
 and client_info like '%user:xx%' and client_info like '%m:zc%';
*/
end pkg_clientinfo;
/

使用測試:

点击(此处)折叠或打开

pkg_clientinfo.set_info('U','testuser');
pkg_clientinfo.set_info('IP',SYS_CONTEXT('USERENV', 'IP_ADDRESS');

問題點: 只能記錄64個字節. 記錄信息需簡潔.

阿飛
2015/04/28