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#);