Drop an oracle user

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.

Cannot drop user ?

When dropping an user, you can get the following error message:

DROP USER myuser CASCADE ;
/*
drop user myuser cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
*/

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.

--
-- Gen_KillUserSessions.sql
--

DEFINE USERNAME_VAR = '&1.' ;

SET VERIFY OFF FEED OFF ECHO OFF;
SET PAGES 0 LINES 128 ;

COLUMN SqlCmd FORMAT A120 WORD_WRAPPED ;
SPOOL KillSessionsFor_&USERNAME_VAR..sql
SELECT 'ALTER USER &USERNAME_VAR. ACCOUNT LOCK ;' SqlCmd FROM DUAL ;
SELECT 'alter system kill session '''||SID||','||SERIAL#||''' immediate ;' SqlCmd FROM v$session WHERE username = UPPER('&USERNAME_VAR.') ;
SPOOL OFF;

-- Uncomment this part below to run the script immediately
-- SPOOL KillSessionsFor_&USERNAME_VAR..log
-- @@ KillSessionsFor_&USERNAME_VAR..sql
-- SPOOL OFF

EXIT ;

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:

DROP USER myuser CASCADE ;
/*
User dropped.
*/

If not, retry it a few minutes later. It can happen that some killed sessions take a while to disappear.

Hope That Helps,
Michel.

Add comment