Kill A Process - Not able to clear session (Doc ID 1493261.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Information in this document applies to any platform.
***Checked for relevance on 20-Sep-2016***

Goal

 

You executed "drop user" by running the command 'drop user myuser cascade;' as sys but it doesn't work.

There is a blocking session from that user.

The blocking session was tracked and trying to kill it with the command:

'alter system kill session '123,19162' immediate;'

As a result of kill session command the status has changed to killed for this session but it still exists and is considered a blocking session.
You may have tried to use the disconnect command using syntax similar to this: 'alter system disconnect session '123,19162' immediate;' but you get the error: ORA-00031: session marked for kill.
If you try to kill the session using a windows command from the cmd line. You should find the spid of this session. If you run: 'orakill <ORACLE_SID> <spid>', you may see 'Kill of thread id <spid> in instance <ORACLE_SID> successfully signalled' the session still exists and you cannot drop the user.

Is there another way to somehow make this session disappear in order to drop the user without shutting down the database and restarting it?

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms