Manage/kill sessions in Oracle

A connection is a network link connection to the database. A connection can have 0, one or many sessions, but the normal situation is only one session per connection.
The tasks in a session are executed by processes. But a process does not have to be dedicated to a specific session.

If you want to see the current active sessions, select the v$session table:

SQL> select s.sid, s.serial#, s.type, s.status, s.username, 
     s.schemaname from v$session s

If you want to see the current processeses running in background, you have to search also in the v$process table:

SQL> select s.sid, s.serial#, p.spid, s.username, s.schemaname, 
  s.program, s.terminal, s.osuser, s.status
  from v$session s join v$process p 
  on s.paddr = p.addr  where s.type != 'BACKGROUND';

The output will be something like this:

SID SERIAL# SPID  USERNAME   SCHEMANAME    PROGRAM                   TERMINAL OSUSER   STATUS
--- ------- ----- --------- ------------ --------------------------- -------- ------- -----
142   56088 8135   SYS        SYS         sqlplus@mydb02 (TNS V1-V3)  pts/1    oracle   ACTIVE
131   37655 7732   SYS        SYS         sqlplus@mydb02 (TNS V1-V3)  pts/2    oracle   INACTIVE
44    47015 7997   DBUSER     DBDATA      JDBC Thin Client            unknown  manager  ACTIVE
15    48699 7999   DBUSER     DBDATA      JDBC Thin Client            unknown  manager  KILLED
[...]

If you want to kill a session just write use the command alter system kill session ‘sid,serial#’, for example:

SQL> alter system kill session '142,56088';

This will kill the session once it has finished the current ongoing transaction. If you want to stop it now, add the word immediate at the end:

SQL> alter system kill session '142,56088' immediate;

This will stop the ongoing transaction and will start the roll back of the changes made in that transaction. This can take a while if it has to move a lot of data, so the process will change its status to KILLED until the roll back finish, then it will dissappear.

Another form to kill a session is to terminate the PID for the running process. In linux, in the console you can write

$ kill -9 SPID

where SPID is the process id of the desired process that was shown in the previous table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s