Session Management In Oracle

Monitoring

sql> select count(*) from v$session where username is not null;

To get session details:

Sql> select username,sid,serial#,status,logon_time from v$session;

Userswise sessions:

Sql> select username,count(*) from v$session group by username;

Joining v$process&v$session

Sql>select a.sid,a.serial#,a.username,b.pid,b.spid from v$sessiona,v$process b where a.paddr=b.addr and username =’HAS’;

Killing session at database level :

Syntax:

alter system kill session (‘SID,SERIAL#’)

ex. Sql>Alter system kill session ‘146,4’;

Sql>select username,to_char(logon_time,’hh24:mi:ss dd-mon-yy’),sid,serial# from v$session where sid=’146’;

Sql>Alter system kill session ‘146,4’;

Killing session from oslevel :

At os level:

$ps –ef |grep 3657

Kill -9 3657 

Imporatnt v$ views:

V$process, v$session,  v$sqlarea,  v$sqltext, v$lock

V$session_wait ,V$sess_io