Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

Killing Sessions in Oracle

Sometimes Oracle Database Administrators needs to kill a sessions in Oracle Database. The best way to do it is to ALTER SYSTEM KILL SESSION statement. To use this statement we must provide SID and SERIAL# of the session we are going to kill.

We can find the SID and SERIAL# using below query:

SQL> select sid, serial#, username, osuser, status

2 from v$session
3 where username is not null
4 /

SID SERIAL# USERNAME OSUSER STATUS
170 6 U1 oracle INACTIVE
200 5 SYS oracle ACTIVE

SQL> alter system kill session '170,6'
2 /
System altered.

SQL> select sid, serial#, username, osuser, status
2 from v$session
3 where username is not null
4 /

SID SERIAL# USERNAME OSUSER STATUS
170 6 U1 oracle KILLED
200 5 SYS oracle ACTIVE

In user session, that was killed we will see the following when we try to execute the query
SQL> select * from dual
2 /
select * from dual
*
ERROR at line 1:
ORA-00028: your session has been killed

Sometimes Oracle is not able to kill the session immediately, in such cases the session is marked for kill. Oracle will kill that session as soon as possible.
User session cannot be interrupted while it is performing network I/O or rolling back transaction, hence there is a possibility to add IMMEDIATE parameter to ALTER SYSTEM KILL SESSION statement. IMMEDIATE parameter tells Oracle to terminate the session without waiting for outstanding activity to complete.

User Session:
SQL> conn u1/u1@orcl
Connected.
SQL> create table t100 as
2 select * from all_objects
3 where 1 = 0
4 /

Table created.

Admin Session:
SQL> select sid, serial#, username, osuser, status
2 from v$session
3 where username is not null
4 /

SID SERIAL# USERNAME OSUSER STATUS
171 29 U1 oracle INACTIVE
200 5 SYS oracle ACTIVE


User Session:
SQL> insert into t100
2 select * from all_objects
3 /

Admin Session
SQL> alter system kill session '171,29' immediate
2 /
System altered.
User Session
select * from all_objects
*
ERROR at line 2:
ORA-03113: end-of-file on communication channel
Process ID: 5449
Session ID: 171 Serial number: 29

Using ALTER SYSTEM KILL SESSION is suggested way of killing an Oracle session, however we can also kill the session from operating system level. On Windows NT we can use orakill program, and on Linux / Unix systems we can use kill -9 command. Before we use those commands we need to identify operating system process id, to do it we can use the following query.

SQL> select s.sid, s.serial#, s.username,s.status, s.osuser, p.spid

2 from v$session s, v$process p
3 where s.paddr = p.addr and s.username is not null
4 /

SID SERIAL# USERNAME STATUS OSUSER SPID
200 5 SYS ACTIVE oracle 4796
167 114 U1 INACTIVE oracle 6031

On Linux / Unix:
[oracle@localhost ~]$ ps aux | grep 6031 | grep -v grep
oracle 6031 0.0 1.1 599856 18308 ? Ss 01:42 0:00 oracleorcl (LOCAL=NO)
[oracle@localhost ~]$ kill -9 6031
In User Session
SQL> select * from dual
2 /
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 6031
Session ID: 167 Serial number: 114

On Windows, orcl is instance SID and 4924 is spid selected using above query.
C:\Documents and Settings\mob>orakill orcl 4924 Kill of thread id 4924 in instance orcl successfully signalled.

Categories: