Finding and Killing Sessions in Oracle
To be able to find and kill sessions in an Oracle database, the user must first be logged in with DBA privileges to gain access to the tables necessary to find the sessions and the command necessary to kill the session. To connect to Oracle with DBA privileges using RazorSQL, make sure to select the "SYSDBA" connect as option when creating the Oracle connection profile on the Connections -> Add Connection Profile menu option.
After connecting with DBA privileges, the following query can be used to find the running sessions:
SELECT
*
FROM
SYS.v_$session s,
SYS.v_$sess_io io,
SYS.v_$process p
WHERE
s.sid=io.sid AND
s.paddr = p.addr
The key columns to look at in the above query are the SID, SERIAL#, and USERNAME columns. After finding the session from the query results after executing the above query, the SID and SERIAL# are needed to be able to kill the session. To kill the session, execute the following command passing in the SID and SERIAL# of the session you want to kill. Below is an example query to kill a session. The SID is the first value and the SERIAL# is the second value.
ALTER SYSTEM KILL SESSION '52,3333'
Sessions can also be disconnected using the following command that also takes the SID and SERIAL#:
ALTER SYSTEM DISCONNECT SESSION '52,3333' POST_TRANSACTION
