KIling Oracle DB Connections

Had to kill an application user with thousands of connections from different app servers.

The code from the link below did the job with a slight amendment.

https://www.oracledocs.com/generating-query-to-kill-session-from-sql_id/

SELECT 'alter system kill session '

|| ''''

|| SID

|| ','

|| SERIAL#

|| ' immediate ;'

FROM v$session

WHERE sql_id = '&sql_id';

For RAC instances

SELECT 'alter system kill session '

|| ''''

|| SID

|| ','

|| SERIAL#

|| ',@'

|| inst_id

|| ''''

|| ' immediate ;'

FROM gv$session

where username='APP_USER' and machine in ('evc01','evc02','evc03')

Simply spooled the output - checked it was what we wanted - changed the .lst file to .sql and used that to kill all the sessions. Hit an ORA-00031 error when killing the session - Burleson gives more detail.

ORA-00031: session marked for kill.

Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done.

Action: No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.