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
/
