My Oracle Support Banner

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

Last updated on DECEMBER 27, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 12-Mar-2018***

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.