ORA-01940: cannot drop a user that is
currently connected
There may be situations we need to drop
schemas on request. What if, we try to drop and user sessions connecting
continuously from application end. Though we kill session connection
establishes again. We have one option, first is to LOCK the user account (This
tip given by my friend Mohammed Gani sait) simple but will not strike when we
are trying urgently
Just to
stimulate the error the following illustration
SQL> create user drop_user identified by drop123
2 default tablespace users
3 ;
User created.
SQL> grant create session to drop_user;
Grant succeeded.
--established few connections--
SQL> select username,osuser,sid,serial#,process from v$session where username='DROP_USER';
USERNAME OSUSER SID SERIAL# PROCESS
------------------------------ ------------------------------ ---------- ---------- ----------------
DROP_USER ASIAPACIFIC\athil 8 21 9120:7896
DROP_USER ASIAPACIFIC\athil 53 21 7488:8436
DROP_USER ASIAPACIFIC\athil 93 23 6128:2724
DROP_USER ASIAPACIFIC\athil 95 15 9116:1436
SQL> drop user drop_user;
drop user drop_user
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
--Dynamic query for killing sessions --
SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' "Kill string" FROM v$session where username='DROP_USER';
Kill string
--------------------------------------------------------------------------------
alter system kill session '15,9' immediate;
alter system kill session '50,13' immediate;
alter system kill session '53,3' immediate;
alter system kill session '98,21' immediate;
SQL> alter system kill session '15,9' immediate;
System altered.
SQL> alter system kill session '50,13' immediate;
System altered.
SQL> alter system kill session '53,3' immediate;
System altered.
SQL> alter system kill session '98,21' immediate;
System altered.
Killed all above four sessions and tried to drop the schema
SQL> drop user drop_user;
drop user drop_user
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
There is a new connection again
SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' "Kill string" FROM v$session where username='DROP_USER';
Kill string
--------------------------------------------------------------------------------
alter system kill session '15,25' immediate;
Locked the schema account
SQL> alter user drop_user account lock;
User altered.
SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' "Kill string" FROM v$session where username='DROP_USER';
Kill string
--------------------------------------------------------------------------------
alter system kill session '15,25' immediate;
SQL> alter system kill session '15,25' immediate;
System altered.
SQL> drop user drop_user;
User dropped.
--------------------------------------------------------------------------------
