When you need to drop a user who is not currently connected to the database, the task is trivial (please do not try it if you don’t really need to drop your user):
DROP USER SCOTT CASCADE;
With actively used database user schemas, the above command may fail due to active Oracle sessions opened by that user (ORA-01940: cannot drop a user that is currently connected). So you may want to drop all those sessions first before dropping the user:
DECLARE CURSOR c IS SELECT sid, serial# FROM v$session WHERE username='SCOTT'; v_sql VARCHAR2(200):='ALTER SYSTEM KILL SESSION ''{sid},{serial#}'' IMMEDIATE'; v_kill VARCHAR2(200); BEGIN FOR v IN c LOOP v_kill:=REPLACE(v_sql,'{sid}',v.SID); v_kill:=REPLACE(v_kill,'{serial#}',v.serial#); EXECUTE IMMEDIATE v_kill; END LOOP; END; /
Seems like we are ready now to drop the user? Not so quick! While we were dropping existing sessions, the user might have opened more new sessions, so dropping a user may still fail with the same error:
ORA-01940: cannot drop a user that is currently connected
To work around this issue, we need to apply a simple trick: revoke a privilege to create a session before killing existing sessions! Here is a complete script (to be executed as SYS or SYSTEM):
REVOKE CONNECT FROM scott -- so new sessions will be blocked / --If CREATE SESSION privilege was granted to scott, we may want to revoke it as well --Kill existing sessions: DECLARE CURSOR c IS SELECT sid, serial# FROM v$session where username='SCOTT'; v_sql VARCHAR2(200):='ALTER SYSTEM KILL SESSION ''{sid},{serial#}'' IMMEDIATE'; v_kill VARCHAR2(200); BEGIN FOR v IN c LOOP v_kill:=REPLACE(v_sql,'{sid}',v.SID); v_kill:=REPLACE(v_kill,'{serial#}',v.serial#); EXECUTE IMMEDIATE v_kill; END LOOP; END; / --Drop the user DROP USER SCOTT CASCADE /
One thought on “Nuances when dropping an Oracle user, by Zahar Hilkevich”