View running processes in Oracle DBThis will show you a list of all running processes:
SET LINESIZE 200Identify database SID based on OS Process ID
SET PAGESIZE 200
SELECT PROCESS pid, sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id AND sess.type = 'USER';
use the following SQL query, when prompted enter the OS process PID:
SET LINESIZE 100For making sure you are targeting the correct session, you might want to review the SQL associated with the offensive task, to view the SQL being executed by the session you can use the following SQL statement:
col sid format 999999
col username format a20
col osuser format a15
SELECT b.spid,a.sid, a.serial#,a.username, a.osuser
FROM v$session a, v$process b
WHERE a.paddr= b.addr
ORDER BY b.spid;
SELECTKilling the session
v$sqltext_with_newlines a, v$session b, v$process c
c.spid = '&spid'
c.addr = b.paddr
b.sql_address = a.address;
The basic syntax for killing a session is shown below.
ALTER SYSTEM KILL SESSION 'sid,serial#';In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';The KILL SESSION command doesn't actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete.
In addition to the syntax described above, you can add the IMMEDIATE clause.
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.
If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it's worth checking to see if it is performing a rollback.