Force drop user in Oracle database

  Uncategorized

If you want to drop a oracle user with the DROP USER statement to remove a database user and optionally remove the user’s objects you can execute ‘drop user @USERNAME cascade’. But if you have a problem to drop the user and you get the error “ORA-01940: cannot DROP a user that is currently logged in”. The problem is that the user is not connected according to v$session. I suspect that it’s related to my replication, and the user is a propagator. How do I get around this ORA-01940 error?

When you drop a user, Oracle Database also purges all of that user’s schema objects from the recycle bin.


DECLARE
open_count integer;
BEGIN
-- prevent any further connections
EXECUTE IMMEDIATE 'alter user @USERNAME account lock';
--kill all sessions
FOR session IN (SELECT sid, serial#
FROM v$session
WHERE username = '@USERNAME')
LOOP
-- the most brutal way to kill a session
EXECUTE IMMEDIATE 'alter system kill session ''' || session.sid || ',' || session.serial# || ''' ';
END LOOP;
-- killing is done in the background, so we need to wait a bit
LOOP
SELECT COUNT(*)
INTO open_count
FROM v$session WHERE username = '@USERNAME';
EXIT WHEN open_count = 0;
dbms_lock.sleep(0.5);
END LOOP;
-- finally, it is safe to issue the drop statement
EXECUTE IMMEDIATE 'drop user @USERNAME cascade';
END;