Pages

Friday, September 21, 2012



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.

--------------------------------------------------------------------------------