oracle中用户连接相关

查看用户连接

断掉某一连接

查看连接ip

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。。。

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的进程号