oracle用户连接相关

oracle中查看用户连接
select username,sid,serial#,status from v$session where username <>'1';

select sid,ses.serial#,ses.username,pro.username,pro.spid,status
  from v$session ses,v$process pro
  where ses.username='PRODUCT' and ses.paddr=pro.addr;

select ses.username,pro.* from v$process pro,v$session ses where ses.paddr=pro.addr and  ses.username<>'1';
从上面的sql中可以获得连接的用户、oracle sid以及os的进程号等信息
利用上述信息，可以停掉连接
alter system kill session 'sid,serial#';或者根据os的进程号直接kill
上述sql无法获得连接的client的ip，不知道有没有现成的view能提供呢？

要获取ip
方法1：$ORACLE_HOME/network/log/listener_orarac1.log
方法2：需要建立logon的trigger
create table session_history
(username varchar2(20),
log_time date,
ip varchar2(20)
)

CREATE OR REPLACE TRIGGER logon_history
AFTER LOGON ON database
BEGIN
insert into session_history
select username,SYSDATE,SYS_CONTEXT('USERENV','IP_ADDRESS') from v$session where audsid = userenv( 'sessionid' ) ;
commit;
END;

方法3：netstat -anp |grep 1521
tcp        0      0 192.168.1.52:1521           192.168.2.101:45877         ESTABLISHED 5582/oracleorcl1
tcp        0      0 192.168.1.52:1521           192.168.2.101:37343         ESTABLISHED 5588/oracleorcl1
tcp        0      0 192.168.1.52:1521           192.168.2.101:50172         ESTABLISHED 24184/oracleorcl1
tcp        0      0 192.168.1.52:1521           192.168.2.101:59023         ESTABLISHED 5584/oracleorcl1
tcp        0      0 192.168.1.52:1521           192.168.10.23:1992          ESTABLISHED 29055/oracleorcl1
tcp        0      0 192.168.1.52:1521           192.168.2.101:51121         ESTABLISHED 5586/oracleorcl1
tcp        0      0 192.168.1.52:1521           192.168.2.102:44376         ESTABLISHED 18104/oracleorcl1
tcp        0      0 192.168.1.52:1521           192.168.2.102:51209         ESTABLISHED 27165/oracleorcl1
tcp        0      0 192.168.1.52:1521           192.168.2.102:59845         ESTABLISHED 18102/oracleorcl1
tcp        0      0 192.168.1.52:1521           192.168.2.102:41867         ESTABLISHED 22780/oracleorcl1
其中5582/oracleorcl1中的5582就是os的进程号

强行断开某一用户的超时连接
CREATE OR REPLACE PROCEDURE P_KILLSESSION(FREETIME NUMBER DEFAULT 3600,USER_NAME VARCHAR2)
AS
  v_Str VARCHAR2(100);
  CURSOR C_users(v_time number,v_user varchar2) IS
    SELECT 'alter system kill session ' || '''' || s.sid ||','||s.serial# ||'''' operates
      FROM v$session s, v$process p
      WHERE TYPE = 'USER' and s.username=upper(v_user) AND p.addr = s.paddr AND status != 'KILLED' AND last_call_et > v_time;
BEGIN
  FOR T_users IN C_users(FREETIME,USER_NAME) LOOP
    v_Str := T_USERS.OPERATES;
    EXECUTE IMMEDIATE v_str;
  END LOOP;
END;
/

查看用户执行的sql：
select sid,ses.serial#,ses.username,pro.username,pro.spid,status
  from v$session ses,v$process pro
  where ses.username='ICC' and ses.paddr=pro.addr;

select  b.sql_text, --SQL內容
a.MACHINE, --哪台机器運行的SQL
a.USERNAME, --用戶
a.MODULE, --運行方式
c.sofar/totalwork*100,  --工作執行了百分之多少
c.elapsed_seconds,   --己?用了多少(秒)
c.time_remaining   --剩多少(秒)
from v$session a, v$sqlarea b,v$session_longops c
where a.sql_hash_value=b.HASH_VALUE(+) and a.sid=c.sid(+) and a.SERIAL#=c.SERIAL#(+) and a.username='ICC';


select user_name,sql_text from v$open_cursor
　where sid in (select sid from (select sid from v$session where status='ACTIVE' and username='ICC'));

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/32939/viewspace-566920/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/32939/viewspace-566920/