We didn't have Enterprise Manager available, so it was down to doing things manually.
First, to identify the currently running SQLs:
SELECT SID, SERIAL#, SQL_ID
FROM GV$SESSION
WHERE USERNAME IS NOT NULL AND STATUS = 'ACTIVE' AND SQL_ID IS NOT NULL;
Using the output from the above query, we need to find the correct SQL ID. The query below will do that. Use the SQL_ID from the output and replace {sql_id}, these should look something like '1ka9536avjyuc'.
SELECT SQL_TEXT
FROM V$SQL
WHERE SQL_ID = '{sql_id}';
Running two queries is a bit tedious, so simplifying this into one query:
SELECT S.SID, S.SERIAL#, Q.SQL_TEXT
FROM GV$SESSION S, V$SQL Q
WHERE S.USERNAME IS NOT NULL
AND S.STATUS = 'ACTIVE'
AND S.SQL_ID IS NOT NULL
AND Q.SQL_ID = S.SQL_ID;
Now this gives us the SID and SERIAL# that we need to kill the session.
Using the SQL below the session can be terminated, replace the {sid} and {serial#} that corresponds to the query that should be killed. This will kill the entire session, so if the query that is causing trouble is a part of a script, the entire script will lose its connection to the Oracle Database.
ALTER SYSTEM KILL SESSION '{sid},{serial#}';
-i
