Tag Archives: session

Oracle: who is connected to the server

Posted on by
Reply

Here is the simple query, showing who is connected to the server.


col username for a15
col program for a25 trun

select sid, serial#, username, program, substr(module,1,10) module, logon_time
from v$session
order by 1,2;

This is almost the same query, limiting the output to the active users:

select sid, serial#, username, program, substr(module,1,10) module, logon_time
from v$session
where status = 'ACTIVE'
order by 1,2;

It’s important to set “MODULE” field for the running sessions. It makes the output more clear.
The setting could be done with the following command:


exec DBMS_APPLICATION_INFO.SET_MODULE( module_name=>'&module',action_name=>'&action' );

Oracle: kill session

Posted on by
Reply

The command to kill Oracle is simple:


ALTER SYSTEM KILL SESSION '&sid,&serial';

sid – session ID
serial – session serial number

 

If the version 11g and this is RAC, additional syntax is possible:

ALTER SYSTEM KILL SESSION '&sid,&serial,@&inst_id';

inst_id – instance id number (1, 2, 3 …)

 

Script to find the TOAD session older than 1 day and kill them:

select
'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' immediate;' CMD
from v$session
where
program like '%TOAD%'
and logon_time and status != 'KILLED'
;

 

If the session is killed on the Oracle level and still stays in the list for very long time with status ‘KILLED’, it could be killed on the OS level.
Warning! This is potentially dangerous operation! Use it only as the last resort.


select sess.sid, sess.serial#,
'OS:' || proc.spid SPID,
'App:' || sess.process process,
substr(sess.username,1,15) username ,
sess.program,
logon_time ,
sess.status
from v$session sess, v$process proc
where sess.paddr = proc.addr
and sess.status = 'KILLED'
order by 1,2;

Check if “App:” process still exist – maybe, it’s worth to stop the application first.
Otherwise, kill the “OS:” process.