CODEWITHSUNDEEP
oracle-databasesqlplus
David Crane
David Crane

Reputation: 93

Why does my KILL SESSION command disconnect me?

Using sqlplus as my client:

SQL> select count(*) from TABLE;

  COUNT(*)
----------
     26243

SQL> alter system kill session '9997,55';
alter system kill session '9997,55'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

That session identifier is not that of my current session, it is a problem session. I then have to exit sqlplus and reconnect to the database. My session is not killed.

Finding documentation is super hard - the docs don't specify anything about a disconnection on a kill attempt, and "alter system disconnect session" is all that comes up as soon as I add the word "disconnect" to my queries.

Can someone help explain what's going on?

Definitely not killing my current session:

SQL> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
301

SQL> alter system kill session '3501,591';
alter system kill session '3501,591'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Upvotes: 0

Views: 456

Answers (1)

Gary Myers
Gary Myers

Reputation: 35401

When you connect your client to the database server, it starts a session on the server. That session may execute zero or more transactions and then (typically) the client disconnects with the database server ending the session normally.

Sometimes you'll disconnect your client abnormally (eg a ctrl-c or computer reboot) and the database server will notice the client stops communicating and will rollback any current transaction for the session and then ends it normally.

A KILL SESSION should be rarely required, and is normally executed by a DBA's session to end a client session that is causing a problem when the DBA can't get the user to end their client session. The end user will get the error you see above, saying the database session is no longer available.

It looks like you are running a KILL SESSION on your currently connected session. There's no need to do this. If you are trying to kill a different session, you need to use the SID/SERIAL# numbers for the session you want killed.

Upvotes: 2

Related Questions