How to Kill an Oracle session

Very useful for dropping the repository schema when you are the repo user is logged in. It helps overcome this error message for example:

DROP USER SEMARCHY_REPOSITORY CASCADE
Error report -
SQL Error: ORA-01940: cannot drop a user that is currently connected
01940. 00000 - "cannot drop a user that is currently connected"
*Cause: Attempt was made to drop a user that is currently logged in.
*Action: Make sure user is logged off, then repeat command.

– More info here:

http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

Find the session corresponding to Semarchy (Tomcat) connecting to the Data Location or Schema you want.

Non-AWS RDS environment

Normally, kill a job with this command (outside of AWS RDS):

alter system kill session '17,5'; --of course you must replace 17,5 with appropriate sid,serial#

AWS RDS environment

Amazon RDS does not allow this alter system command. They provide this alternative syntax:

exec rdsadmin.rdsadmin_util.kill(sid, serial#);