Sometimes it may happen that you will not be able to drop an Oracle user. It happens when there are still opened sessions on it.
This article will show you how to kill all theses sessions in order to drop this user.
PLEASE, READ THIS FIRST
This is a critical manipulation which will be quite hard to revert without a backup. So make sure you are on the right database and you have a working backup.
I decline all responsibility for loss or damage on your database.
Cannot drop user ?
When dropping an user, you can get the following error message:
You will get this error if there still are sessions connected with that user. All these sessions have to be closed in order to be able to drop the user. First, you should close manually all applications connected with the concerned user. If you can still not drop the user, then continue to the next part.
Kill all user's sessions
The following script will generate a script to kill all the connected sessions for the specified user.
Save the code above into "Gen_KillUserSessions.sql" file than run it as sysdba or as the user. The generated script will first lock the user then kill all its sessions. It has to be run as sysdba.
Drop user without error
Finally you will normally be able to drop the user with the following command:
If not, retry it a few minutes later. It can happen that some killed sessions take a while to disappear.
Hope That Helps,
Enjoyed this article? Please like it or share it.